For every row return the Last Year and Quarter ASP

[emarome94]

Hi guys,

I have a fact table like the one below:

Date             Revenue Quantity 

31/01/2021  1000       500

28/02/2021  2000       500

....

31/12/2021  3000       100       

31/01/2022  4000       200

28/02/2022  5000       100

...

The fact table is connected to a data table: my goal is calculate the ASP (Revenue / Quantity) and set the ASP from the 31/12/2021 (30.00)  for every row in the 2022 having a result like the following

Date             ASP

31/12/2021  30.00

31/01/2022  30.00

28/02/2022  30.00

Can someone help me to figure out a simple measure to be used with the same logic in a report?

Thank you in advance,

E

[emarome94 · Accepted solution]

I solved my issue with the following formula 

ASP_Baseline =

calculate ( [Revenue] / [Quantity], DATESBETWEEN( 'DateTable'[Date], DATE( 2022, 04, 01), DATE( 2022, 06, 30) ) )

The one above is the last quarter in a fiscal year of course.

Thanks for the hint @A_Ben_Chaoued 

[A_Ben_Chaoued]

ASP_21 = calculate ( Revenue / Quantity, 'Table'[DATE] =31/12/2021) return 

IF ('Table'[DATE] <=31/12/2021,Revenue / Quantity, ASP_21)