cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sebastian
Advocate II
Advocate II

The last 12 Month from Today

Hi all,

 

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

 

Thank's a lot 🙂

1 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:

 Capture.PNG

View solution in original post

12 REPLIES 12
procyon82
Resolver I
Resolver I

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

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

Don't know why this didn't works

 

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

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 

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

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

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.

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:

 Capture.PNG

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
Frequent Visitor

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

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
Frequent Visitor

@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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors