The last 12 Month from Today

[Sebastian]

Hi all,

did someone have an idea how to get the last 12 Months from today?

Thank's a lot 🙂

[procyon82]

How about DATEADD('Data'[Date],-12,MONTH) ?

[kdejonge]

well that will only give you a single number 1 year in the past, it will not determine when the value is in range 

[Sebastian]

Don't know why this didn't works

12 Month = CALCULATE(SUM(Tab1[Sales]);Tab1[Date]>=DATEADD(LASTDATE(Tab1[Date]);-12;MONTH))

[kdejonge]

This is a very different calculation and that formula will do something completly different, it will try to calculate the sum of sales for the last 12 months for the current date. It will not show the current sales for the current date.

Then there is another problem, in this case you need to overwrite the filter context to get it to work. Check out this blog post for more info: http://www.radacad.com/secret-of-time-intelligence-functions-in-power-bi 

[Sebastian]

Thanks, that works great 🙂

By the way

I tried          Dateadd(Date[Datecolumn],-12,Month)    but it didn't shows anything

and if i used it in a Calculate function it didn't works.

[kdejonge]

I take it you dont just want the date? Do you want to use it in a chart or as a number? And do you really want today or the last date there was any data?

Thanks,

Kasper

[Sebastian]

Yes, I want to use this in a chart.

As is X Axis the current Month and the 11 which where before and as Value the total amount of each month.

But i find no way to show the last 12 Months as Axis from today on.

[kdejonge · Accepted solution]

You can do something like this:

Sales12 =
var vrlastdate12mnth = CALCULATE(DATEADD( LASTNONBLANK(FactInternetSales[DueDate] ,SUM(FactInternetSales[SalesAmount])) ,-12 ,MONTH) , ALL(FactInternetSales))
return IF(MAX(DimDate[FullDateAlternateKey]) > vrlastdate12mnth , SUM(FactInternetSales[SalesAmount]))

This will check if the last date you have sales for is between then and 12 months before, if so it returns the sales else nothing.

Here you can see it as the hihglighted measure:

 

[nefasto89]

Hi, i tried this DAX and it works with the single rows, but when it comes to summarize the formula ignore the 12 months 
i attacched the formula with the results
https://imgur.com/cnDsDFt

[dMac314]

This is exactly what I needed, you saved me a lot of time and heart ache!! Thank you so much!!

[tylerbauer]

I tried to implement the solution you posted but I got syntax errors. What is the simplest way to return a value for the last 12 months if all date and value information is stored in one table?

[JohnD2]

@kdejonge

Hi,

I tried your solution on my tables and they work fine.

What I get as totals is the total amount of the whole range, so more than the 12 months.

Is it also possible to have the SUM for that specific range? which is 12 months?

I tried something with ALLEXCEPT but that dod not work out.

With that i am able to create some PBI-card about specific date ranges, like day, week, month and so on.

thanks for your help

John.