Calculating for the past twelve month and twelve-month period one year ago

[Anonymous]

Hey as you can see from bottom picture I manage to get a 12 month period of date in the past 12 months. This was done by using Power Bi own relative data filter, also tried using Dax but this was faster. One issue now is that I wanted to have another 12-month period, but just started from 2 years ago, so in this case "2018", and then 12 months forward from there. This means that the measures would meet in the middle. I have posted another picture of my issue when I wanted to use the relative date just started from 2018 and forward. As you can see it also count the 12 month period from 2019 to 2020.

It should have been from 2018 to 2019 (12 months) & 2019 to 2020 (12 months). I don't want to just filter out the year by selecting because this would mean that every year I have to readjust it so next year it will be 2019 to 2020 and 2020 to 2021. Would really enjoy a solution where it automatically readjusts itself, when we went into the next calendar year. Kindly regards Martin 

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

First you need to create a slicer table using a dax expression as below:

SlicerTable = GENERATESERIES(0,12,1)

Then you need a measure as below:

Measure = var a = YEAR(TODAY())-SELECTEDVALUE(SlicerTable[InLastYear]) 
return CALCULATE(SUM('Table'[Value]),FILTER('Table',YEAR('Table'[Date])<a),ALLSELECTED('Table'))

Finally you will see:

 For the relatede .pbix file,pls click here.

Best Regards,
Kelly

[Anonymous]

Hello @Anonymous 

Here is a simple Measure that I ussualy use to set the charts dynamically based on todays date.

This should work for you aswell:

Sales Previous 12 months = 
var MinDate = TODAY()-365
return
CALCULATE([Total Sales];FILTER(FactTable;RELATED(MasterCalendar[Date])>=MinDate))

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.