cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emarome94
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
emarome94
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 

View solution in original post

2 REPLIES 2
emarome94
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 
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
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors