cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

rolling 12 month that starts over at the year

I have a rolling sum calculated column, but my problem is that I really just want it to roll up that year only.  Then when a new year starts, it goes back to zero.  Here is the formula for the column. How can I get this to check the year?

 

Rolling= calculate (sum([count]),

filter(

        all('Turnover No team'),

       'Turnover No team'[Year.Month]<= earlier('Turnover No team'[Year.Month])

))

 

 

ss1.GIF

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: rolling 12 month that starts over at the year

Hi @joshcomputer1

 

Try adding a check on the year to the condition in the filter expression:

 

Rolling =
CALCULATE (
    SUM ( [count] ),
    FILTER (
        ALL ( 'Turnover No team' ),
        'Turnover No team'[Year.Month] <= EARLIER ( 'Turnover No team'[Year.Month] )
            && YEAR ( 'Turnover No team'[Year.Month] )
                = YEAR ( EARLIER ( 'Turnover No team'[Year.Month] ) )
    )
)
2 REPLIES 2
Super User
Super User

Re: rolling 12 month that starts over at the year

Hi @joshcomputer1

 

Try adding a check on the year to the condition in the filter expression:

 

Rolling =
CALCULATE (
    SUM ( [count] ),
    FILTER (
        ALL ( 'Turnover No team' ),
        'Turnover No team'[Year.Month] <= EARLIER ( 'Turnover No team'[Year.Month] )
            && YEAR ( 'Turnover No team'[Year.Month] )
                = YEAR ( EARLIER ( 'Turnover No team'[Year.Month] ) )
    )
)
Highlighted

Re: rolling 12 month that starts over at the year

I actually tried that, but I think had the year () wrong somewhere. Thanks!