cancel
Showing results for
Did you mean:
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

## 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 )
```
Thank you for the kudos 🙂

Proud to be a Datanaut!

4 REPLIES 4
Highlighted
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 )
```
Thank you for the kudos 🙂

Proud to be a Datanaut!

Super User IV

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

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

Thank you for the kudos 🙂

Proud to be a Datanaut!

Announcements

#### 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?

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

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