cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jess11_11 Regular Visitor
Regular Visitor

12 Month Rolling Annual Turnover

Hi All, 

 

I've been struggling with this one for a while. I thought i had it but now i'm realising my solution isn't at all dynamic as it doesn't work correctly when i select a slicer value. 

 

Basically i'm trying to calculate rolling annual turnover at each month. No matter what month i select, i need to show 12 months rolling annual turnover from that month. 

For example, if i select Jan 2018 on my slicer, i need to show rolling annual turnover from Feb 2017 to Jan 2018. So no matter what month i select on my slicer, it should roll back 12 months and it should be rolling. 


1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: 12 Month Rolling Annual Turnover

try this, you need to use months from the calendar table in the visuals

Rolling 12M Turnover =
VAR __NrOfRollingMonths = 12
VAR __CurrentDate = MAX ( 'Calendar'[Date] )
VAR __RollingMonths =
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] > EDATE ( __CurrentDate, - __NrOfRollingMonths )
            && 'Calendar'[Date] <= __CurrentDate
    )
RETURN
    CALCULATE ( [Turnover], __RollingMonths )
Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Highlighted
Super User II
Super User II

Re: 12 Month Rolling Annual Turnover

try this, you need to use months from the calendar table in the visuals

Rolling 12M Turnover =
VAR __NrOfRollingMonths = 12
VAR __CurrentDate = MAX ( 'Calendar'[Date] )
VAR __RollingMonths =
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] > EDATE ( __CurrentDate, - __NrOfRollingMonths )
            && 'Calendar'[Date] <= __CurrentDate
    )
RETURN
    CALCULATE ( [Turnover], __RollingMonths )
Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

View solution in original post

Super User IV
Super User IV

Re: 12 Month Rolling Annual Turnover

jess11_11 Regular Visitor
Regular Visitor

Re: 12 Month Rolling Annual Turnover

Hi @Stachu 

Thank you so much! Is there a way to make it so that when i select a month on my report level slicer, it shows the rolling turnover 12 months prior. I'm getting the correct output, but when i slice, it just shows that one month that i select, but i want it to show the last 12 months from the selected date. 

Super User II
Super User II

Re: 12 Month Rolling Annual Turnover

hmm, that's doable, but adds a lot of complexity. The solution I that comes to mind is having the slicer without active joins to data (basically a new table just for this use case)


other than additional table, the complexity is following - because there would be no active joins you would have to manually recreate the default join behaviour (i.e. having 201902 selected filters for that month). In practice you could use USERELATIONSHIP to activate the join (it has to be deactivated by default) for regular Turnover and all other measures that don't require the rolling logic as you define it

 

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors