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)

Announcements

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!