Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
joshcomputer1
Helper V
Helper V

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
AlB
Super User
Super User

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

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

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

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

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.