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.
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.