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

[LaurieS]

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!

[Jihwan_Kim · Accepted 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 )
    )
)

[LaurieS]

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