Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
16 | |
13 |