Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I've been trying various DAX statements to obtain a rolling 6 month sum, only including months that have data. So far, I'm unable to get it working properly. If I only filter the date slicer, the measure works correctly, as the data is saturated enough to not have any blank months of data.
But if I filter on a country that doesn't have data every month, then it's still using every month to sum the last 6, not just the ones with data.
For example in this scenario Jan-20 should be 10, skipping Nov-19 since there's no data. I would appreciate any feedback. This is the example of the DAX I'm currently using for this:
This is not the only DAX snippets I've used. I think I've tried at least 20+ various community suggestions but not finding any that actually work the way I need it to.
Thanks for your input!
Solved! Go to Solution.
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file whether it suits your requirement.
WINDOW function (DAX) - DAX | Microsoft Learn
Sales measure: =
SUM( Sales[Sales] )
expected result measure: =
CALCULATE (
[Sales measure:],
WINDOW (
-5,
REL,
0,
REL,
SUMMARIZE (
ALLSELECTED ( Sales ),
'Calendar'[Year-Month sort],
'Calendar'[Year-Month]
),
ORDERBY ( 'Calendar'[Year-Month sort], ASC )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file whether it suits your requirement.
WINDOW function (DAX) - DAX | Microsoft Learn
Sales measure: =
SUM( Sales[Sales] )
expected result measure: =
CALCULATE (
[Sales measure:],
WINDOW (
-5,
REL,
0,
REL,
SUMMARIZE (
ALLSELECTED ( Sales ),
'Calendar'[Year-Month sort],
'Calendar'[Year-Month]
),
ORDERBY ( 'Calendar'[Year-Month sort], ASC )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you so much!! Works like a charm. I hadn't tried any DAX using the WINDOW function before. Very clean.
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
87 | |
47 | |
30 | |
22 |