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

[Ciria]

Dears:

I have a problem I cannot get the solution by my own, except using a slicer with "Date[Year]" and narrowing down the context.

Here the picture from my chart:

My goal is to change my Total Sales LY function somehow to avoid Year = 2018 appears, and this needs to be done dynamically, not using either a slicer or something like Calculate( expression; Year <= "2018")....

The aim is not being on the need to amend the measure every year.

Total Sales LY = CALCULATE(SUM(Sales_fact[Total_Euros]);DATEADD('Date'[Date];-1;YEAR))

Here the file for your perusal:

https://www.dropbox.com/s/88dtadb0s7m1hpd/Dummy%20Model.pbix?dl=0

Kind regards!

[Anonymous]

@Ciria,

Change your measure to the following:

Total Sales LY = IF(MAX('Date'[Year])<YEAR(TODAY()); CALCULATE(SUM(Sales_fact[Total_Euros]);DATEADD('Date'[Date];-1;YEAR)))

Regards,
Lydia

[Ciria · Accepted solution]

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?

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,

[Anonymous]

@Ciria,

Do you use same fields to create Area chart and table visual? I would recommend you open a new thread about this issue since it is a new issue which is different from the original question.

Regards,
Lydia