Rolling Calculation Help Needed

[heidibb]

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. Ultimately, I would like a three trimester rolling total on each line. For example, Summer 2017-2018, I would like that to be 3676+3848+4826.

I have tried the following, but I can't figure out how to get it to total what I ultimately want. Note that I'm not the best with ALLEXCEPT. Trimester and Sequence comes from one data connection and all the filters on the left of the page come from another. The data model has them joined with a Many to One cardinatlity, Cross-filter direction = Single 

[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.

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