topic Re: Getting the Last Date Data of Previous Year in DAX Commands and Tips

Getting the Last Date Data of Previous Year

angsoka — Fri, 29 Jul 2022 18:47:41 GMT

I want to create two index which are the ratios of :
1. the price and the base price (the price of 1 December 2018) and
2. the price and the last year price (the price of December of last year)

However, using DAX, I could not get these two columns (Base Price and Last Year Price). I have tried DATEADD, SAMEPERIODLASTYEAR, and didn't get the correct answers.

Date	Price	Base Price	Last Year Price
12/1/2018	100.00
1/1/2019	102.23	100.00	100.00
2/1/2019	103.01	100.00	100.00
...	...	...	...
12/1/2019	108.99	100.00	100.00
1/1/2020	110.00	100.00	108.99
2/1/2020	112.22	100.00	108.99
...	...	...	...
12/1/2020	115.53	100.00	108.99

Thanks for all your help

Re: Getting the Last Date Data of Previous Year

tamerj1 — Fri, 29 Jul 2022 19:26:50 GMT

Hi angsoka
Here is a sample file with the solution https://www.dropbox.com/t/FmGZyCUtYuzgWT9E

Base Price = VAR AllSelectedData = ALLSELECTED ( Data ) VAR FirstYear = YEAR ( MINX ( AllSelectedData, Data[Date] ) ) VAR FirstYearTable = FILTER ( AllSelectedData, YEAR ( Data[Date] ) = FirstYear ) VAR FirstYearLastDate = MAXX ( FirstYearTable, Data[Date] ) VAR FirstYearLastDateTable = FILTER ( FirstYearTable, Data[Date] = FirstYearLastDate ) VAR Result = SUMX ( FirstYearLastDateTable, Data[Price] ) RETURN Result

Last Year Price = VAR CurrentYear = YEAR ( MAX ( Data[Date] ) ) VAR PreviousYearLastDate = CALCULATE ( MAX ( Data[Date] ), YEAR ( Data[Date] ) = CurrentYear - 1, ALLSELECTED ( Data ) ) RETURN CALCULATE ( MAX ( Data[Price] ), Data[Date] = PreviousYearLastDate, ALLSELECTED ( Data ))

Re: Getting the Last Date Data of Previous Year

CNENFRNL — Sat, 30 Jul 2022 15:38:21 GMT

Re: Getting the Last Date Data of Previous Year

angsoka — Sun, 31 Jul 2022 14:57:45 GMT

Dear tamerj1

Thank you so much. It works like a charm. However, when I filtered the price with the product's name. It doesn't give the correct result. I think because of this ALLSELECTED(), which clears the filter.

ALLSELECTED ( Data )

Could you please advise the right filter so that it will give a different December price for a different product?

Re: Getting the Last Date Data of Previous Year

tamerj1 — Sun, 31 Jul 2022 15:16:09 GMT

angsoka

Sorry I'm not not on my computer right now. I don't remember if the product name is in the same table or not but I will assume it is. I think you want to have this calculation applied separately on each product. In this cas I would suggest to replace ALLSELECTED ( Data ) with ALLEXCEPT ( Data, Data[Product Name] ) this way the logic will be applied to each product separately.