cancel
Showing results for
Search instead 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?

Thank you in advance,

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)

## Helpful resources

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors