Time Intelligence // Problem with Last Year function on Linear & Area Charts

Hi Anonymous

Thanks for the answer, it definately works on my report.

But I have a second question I would like to ask if you don't mind before closing this thread.

• This visual was created using years in Axis.
• I have another visual created in Year-Month and the latest sale was done on August 14th 2017.
  • Doing Total Sales - Total Sales LY, Powerbi is showing me the graph but everything shown beyond that date is consequently wrong.

Here the picture to show such problem:

Here my script to solve the problem (two alternatives given), both work...

& Difference LY for Graphs = 
VAR Lastdatewithsales = LASTNONBLANK('Date'[Date],[Total Sales])
VAR Periodforcalculation= DATESBETWEEN('Date'[Date],MIN('Date'[Date]),Lastdatewithsales) // Option 1: Using Tutorial from https://www.sqlbi.com/articles/compare-equivalent-periods-in-dax/
VAR UsingLastDate = LASTDATE(Sales_fact[Invoice_Date]) 
// Using DAX Measures List https://msdn.microsoft.com/en-us/library/ee634396(v=sql.110).aspx
RETURN

IF(UsingLastDate>=MIN('Date'[Date]),
[Total Sales]-[Total Sales LY])

Do you think this is a good approach? any other suggestion?:smileysad:

What I am learning these days with DAX is that if you want to use calculations for tables or for visuals, you have to take care about this topics, because it could hazy for some users...

What happens if I use this measure on a table with "Family" field leading instead of "Dates", the formula doesn't work as expected:

So here, my solution too:

& Difference LY YTD = 
VAR Lastdatewithsales = LASTNONBLANK('Date'[Date],[Total Sales])
VAR Periodforcalculation = DATESBETWEEN('Date'[Date],MIN('Date'[Date]),Lastdatewithsales) 
RETURN

[Total Sales]- CALCULATE([Total Sales LY],Periodforcalculation)

// IF functions doesn't work here, you don't need an interaction function here, just one single scalar value // IF(Lastdatewithsales>=MIN('Date'[Date]),[Total Sales]-[Total Sales LY])//

On Table instead of Visual (Area Chart): You can see here that IF functions (iterative) is not needed, and it doesn't get what you are looking for on this context.

Here functions like MTD, QTD may work too, but not sure if they will when you don't have populate data beyond August 14th...

Kind regards,:smileyvery-happy: