Getting the Last Date Data of Previous Year

[angsoka]

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

[CNENFRNL]

[tamerj1]

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

[angsoka]

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?

[tamerj1 · Accepted solution]

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