Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
emarome94
Helper I
Helper I

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
Helper I
Helper I

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
Helper I
Helper I

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors