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 mea...
  • 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 )
    )
)

     

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.