Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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?

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.