• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional date measure with DAX

redroller

New Member
Would be grateful for some advice on how to achieve this. Say there are clients who pay for a package of consultations that must be used within a calendar period. For some clients, the period is a quarter, for some clients, it is a half-year, for some it is a year.

I have this measure for the number of distinct consultations in the fact table ServiceHistory:
Distinct Consultations = CALCULATE(DISTINCTCOUNT(ServiceHistory[Activity ID]), ServiceHistory[Interaction Type] = “Consultation”)

The other relevant fields are ServiceHistory[Activity Date] and ServiceHistory[Client Name].

The fact table links to a standard date table and to a client summary index table where ClientSummary[Period] indicates "Quarter" "Half-Year" or "Annual" depending on the period for that client.

I want to have a dashboard that lists all clients as rows with two columns. The first would show # of Distinct Consultations in the period-to-date. For example for a quarterly client, the number of consultations from Oct 1 to Oct 19 (today) and for a half yearly client it would show the number since July 1, and for an annual client since Jan 1. The second column would show the total # of Distinct consultations for the prior period (e.g. Q2 or 1H or previous calendar year depending on the client).

Any idea how this can be done?
Thank you.
 
Back
Top