topic Re: 6Mo Rolling Sum using sequential months, exclulding months with no data in DAX Commands and Tips

6Mo Rolling Sum using sequential months, exclulding months with no data

LaurieS — Fri, 26 Jan 2024 20:23:08 GMT

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:

6MoRollingSum_Runs3 =

VAR DateFilter =

DATESINPERIOD ( CalendarTable[CalendarDate], MAX ( CalendarTable[CalendarDate] ), -6, MONTH )

VAR DateFilterMin =

MINX ( DateFilter, CalendarTable[CalendarDate] )

VAR DateFilterNumMonths =

COUNTROWS (

CALCULATETABLE (

SUMMARIZE ( CalendarTable, CalendarTable[Year], CalendarTable[Month] ),

DateFilter

)

VAR EarliestDateInData =

CALCULATE ( MIN ( 'Table'[TransDate]), ALL ( 'Table ),'Table'[NonZero]= 1 )

RETURN

IF (

AND ( EarliestDateInData <= DateFilterMin, DateFilterNumMonths >= 6 ),

CALCULATE ( SUM ('Table'[NonZero]), DateFilter )

)

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!

Re: 6Mo Rolling Sum using sequential months, exclulding months with no data

Jihwan_Kim — Sat, 27 Jan 2024 04:57:54 GMT

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 ) ) )

Re: 6Mo Rolling Sum using sequential months, exclulding months with no data

LaurieS — Mon, 29 Jan 2024 18:28:39 GMT

Thank you so much!! Works like a charm. I hadn't tried any DAX using the WINDOW function before. Very clean.