cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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?

E

1 ACCEPTED SOLUTION
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
2 REPLIES 2
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
New Member

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

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors