Forum Discussion

heidibb's avatar
heidibb
Icon for Helper IV rankHelper IV
2 years ago
Solved

Rolling Calculation Help Needed

Hello, My goal is to get a rolling total for three trimesters and I'm having some trouble. I have Trimester in my data below as well as a trimester sequence, which is formatted as a whole number. Ul...
Need Help
  • 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.

     

     

     

     

    value total: = 
SUM( data_fact[value] )

     

    WINDOW function (DAX) - DAX | Microsoft Learn

     

    expected result measure: = 
CALCULATE (
    [value total:],
    WINDOW (
        -2,
        REL,
        0,
        REL,
        ALL ( period_dimension[sequence], period_dimension[trimester] ),
        ORDERBY ( period_dimension[sequence], 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.

 

 

 

 

value total: = 
SUM( data_fact[value] )

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

expected result measure: = 
CALCULATE (
    [value total:],
    WINDOW (
        -2,
        REL,
        0,
        REL,
        ALL ( period_dimension[sequence], period_dimension[trimester] ),
        ORDERBY ( period_dimension[sequence], ASC )
    )
)