Forum Discussion

LaurieS's avatar
LaurieS
Icon for Helper I rankHelper I
2 years ago
Solved

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

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's avatar
    Jihwan_Kim
    2 years ago

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

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      LaurieS
      Icon for Helper I rankHelper I
      2 years ago

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