cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Automatic and rolling comparison between the last 12 month and the last 12 months from last year

Dear All,

i`m searching for a solution to calculate several dimension of our sales figures.

In Table “Sales” I have the turnover for each productgroup and country from the last two financial years sorted by month (I don’t need a daily comparison). Starting on 01.07.2017 till 30.06.2019. Now I want to fulfill the table with the sales figures from the last months. Now till 30.09.2019. Next month till 31.10.2019 and so on.

The dimension are as followed:

12: Turnover from the last 12 months (01.10.2018-30.09.2019) compared to

the Turnover from the last 12 month last year (01.10.2017-30.09.2018)

6: Turnover from the last 6 months (01.04.2019-30.09.2019) compared to

the Turnover from the last 6 month last year (01.04.2018-30.09.2018)

3: Turnover from the last 3 months last year (01.07.2019-30.09.2019) compared to

the Turnover from the last 3 month (01.07.2018-30.09.2018)

1: Turnover from last months (01.09.2019-30.09.2019) compared to

the Turnover from the month last year (01.09.2018-30.09.2018)

I would like to have a table in Power bi that calculate from the last actual date. In the excel chart the basis is 30.09.2019. Next month I load the data from 31.10.2019 in Power Bi and the basis of the calculation should be 31.10.2019. In the attached Excel File (20191011 calcuation logic) you can see the logic. I hope you understand it and its clear enough.?

Attached you see my Power Bi tables and the Excel Upload file (20191010 Test).

Excel calculation logic:

PowerBi:

I would be very grateful

Best Regards

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Automatic and rolling comparison between the last 12 month and the last 12 months from last yea

You should able to get all rolling calc in this manner. Change and create different measure as required

```last 12 Months = (
var _Cuur_start = MINX('Compare Date',DATEADD('Compare Date'[Compare Date],-12,MONTH))
VAR _Curr_END = Max('Compare Date'[Compare Date])
var _Last_start = MINX('Compare Date',DATEADD('Compare Date'[Compare Date],-24,MONTH))
VAR _Last_END_END = MAXX('Compare Date',DATEADD(ENDOFMONTH('Compare Date'[Compare Date]),-12,MONTH))

var _last12 = CALCULATE(SUM(Sales[Sales Amount]),Sales[Sales Date]>= _Cuur_start && Sales[Sales Date] <=  _Curr_END )
var _last12_before12 = CALCULATE(SUM(Sales[Sales Amount]),Sales[Sales Date]>= _Last_start && Sales[Sales Date] <=  _Last_END_END )
return
_last12 -_last12_before12 )```

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

4 REPLIES 4
Super User IV

## Re: Automatic and rolling comparison between the last 12 month and the last 12 months from last yea

You should able to get all rolling calc in this manner. Change and create different measure as required

```last 12 Months = (
var _Cuur_start = MINX('Compare Date',DATEADD('Compare Date'[Compare Date],-12,MONTH))
VAR _Curr_END = Max('Compare Date'[Compare Date])
var _Last_start = MINX('Compare Date',DATEADD('Compare Date'[Compare Date],-24,MONTH))
VAR _Last_END_END = MAXX('Compare Date',DATEADD(ENDOFMONTH('Compare Date'[Compare Date]),-12,MONTH))

var _last12 = CALCULATE(SUM(Sales[Sales Amount]),Sales[Sales Date]>= _Cuur_start && Sales[Sales Date] <=  _Curr_END )
var _last12_before12 = CALCULATE(SUM(Sales[Sales Amount]),Sales[Sales Date]>= _Last_start && Sales[Sales Date] <=  _Last_END_END )
return
_last12 -_last12_before12 )```

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Frequent Visitor

## Re: Automatic and rolling comparison between the last 12 month and the last 12 months from last yea

Dear Amitchandak,

thank you for your answer @amitchandak . Is this one big meausre? Or how i have to handle it?

When i try the first block, i get a Syntax Error.

I`m still ja Beginner with Power BI 🙂 So many things in Power BI are new for me.

Best Regards

Super User IV

## Re: Automatic and rolling comparison between the last 12 month and the last 12 months from last yea

Share the formula you created

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Frequent Visitor

## Re: Automatic and rolling comparison between the last 12 month and the last 12 months from last yea

Dear amitchandak,

it seems to work great. Yesterday it was my fault.

Now i set a column with the last 12 month with this meausre:

```Test last 12 Months = (
VAR _Curr_END = Max(Sales[Datum])

var _last12 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Cuur_start && Sales[Datum] <=  _Curr_END )
var _last12_before12 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Last_start && Sales[Datum] <=  _Last_END_END )
return
_last12 -_last12_before12 )```

And another columns who shows me the last 12 months befor the last 12 month from the measure above.

```Test Test last 24 Months = (
VAR _Curr_END = Max(Sales[Datum])

var _last24 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Cuur_start && Sales[Datum] <=  _Curr_END )
var _last24_before24 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Last_start && Sales[Datum] <=  _Last_END_END )
return
_last24 -_last24_before24 )
-
(
VAR _Curr_END = Max(Sales[Datum])

var _last12 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Cuur_start && Sales[Datum] <=  _Curr_END )
var _last12_before12 = CALCULATE(SUM(Sales[Turnover]);Sales[Datum]>= _Last_start && Sales[Datum] <=  _Last_END_END )
return
_last12 -_last12_before12 )```

I dont know if its the easiest way but now i can compare the two columns. Thats all what i need. Thank you @amitchandak 🙂

Best Regards

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors