cancel
Showing results for
Did you mean: Frequent Visitor

## For every row return the Last Year and Quarter ASP

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?

E

1 ACCEPTED SOLUTION Frequent Visitor

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
2 REPLIES 2 Frequent Visitor

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 New Member

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

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