Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have several DAX measures that I currently use in visualizations to track client service levels against Year/Month x-axis parameters from a date table. For example, hours of service provided, count of staff that logged interactions, number of contacts at a client account to receive service. Some of these measures are 12 month rolling averages.
I would like to adapt them into period-over-period % change measures that can all be combined in a matrix/heat map visual (i.e. all accounts in rows, all measures as columns). Not sure how to do this based on a point in time and would appreciate some advice. For example, I have a measure "Service Hours 12MMA" which is a 12 month moving average of service hours provided. I would like another measure that shows the % change in this measure compared to 90 days prior (or alternatively at the end of the last full month vs. 3 months prior).
Thank you for your help.
Thank you for the feedback. I actually have some nested measures (don't have a great grasp of DAX and came up with these modifying different examples).
For example, this measure is to count the number of contacts that have received "High Touch" service more than once. It works by counting the rows of a calculated column where client and sales person's names are concatenated
Frequent Contacts = COUNTROWS(FILTER(SUMMARIZE(FILTER(ServiceDetails, ServiceDetails[Service Type]="High Touch"), ServiceDetails[Concatenate-ClientSales], "Count", COUNTROWS(ServiceDetails)), [Count]>1))
This measure is nested into this function:
Last 12M Frequent Contacts =
CALCULATE(
[Frequent Contacts],
DATESINPERIOD('Date'[Date],
MAX ('Date'[Date] ),
-12,
MONTH ))
I chart this measure as a time series by month on the X-axis of a visualization, so it is effectively a 12MMA.
How then can I calculate the % change in Last 12M Frequent Contacts now vs. 3 months ago? Or have I dont something totally wrong?
Many thanks!
@gambleave , Based on what I got
Assume you have rolling 12 month measure
12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))
Then 3 month behind rolling 12 months will be
12 Month Avg b 3 = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-3) ,-12,MONTH))
User | Count |
---|---|
83 | |
78 | |
62 | |
61 | |
49 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |