Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi 🙂
I have a clustered bar chart to show a count of cases created according to date by location.
I would like the visual to show the average cases from the last 4 weeks.
I am wracking my brain around Time Intelligence DAX, but can't find anything definitive that works!
Any help will be much appreciated!
Solved! Go to Solution.
To solve these time intelligence issues, I maintain a calendar table that has a field that I call 'Week Offset Number'. The file contains an integer value describing the number of weeks into the past the indicated week is. (last week is -1, the week prior is -2).
This makes the DAX to look at the 'last x weeks' or even the 'last x weeks from the selected week' DAX pretty easy.
Average Measure = CALCULATE(AVERAGE(<FACT>), ALL(Calendar), Calendar[Week Offset Num] > -4)
To solve these time intelligence issues, I maintain a calendar table that has a field that I call 'Week Offset Number'. The file contains an integer value describing the number of weeks into the past the indicated week is. (last week is -1, the week prior is -2).
This makes the DAX to look at the 'last x weeks' or even the 'last x weeks from the selected week' DAX pretty easy.
Average Measure = CALCULATE(AVERAGE(<FACT>), ALL(Calendar), Calendar[Week Offset Num] > -4)
You have a Calendar table that includes week numbers. Define what you mean by "last 4 weeks" (does it include the current week or not?) and then write your measure accordingly. Show what you have tried.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |