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'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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
70 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
128 | |
29 | |
28 | |
24 | |
22 |