cancel
Showing results 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

New Member

## How to calculate price variance between two date ranges, with Valid From and valid To dates ?

Hi All,

I have a fact table with Product ID, ValidFrom, ValidTo, & Price columns. I'd like to calculate the price difference compared to previous date range/previous validity period. Also, if there is no previous range to compare there shouldn't be any calculation, so it should give desired result as blanks.

For Example :

 Product ID ValidFrom ValidTo Price A 1/1/2019 2/28/2019 \$     992.30 A 3/1/2019 7/10/2019 \$     984.20 A 7/11/2019 8/31/2019 \$  1,002.50 A 9/1/2019 10/31/2019 \$     986.20 B 1/1/2019 8/31/2019 \$     570.20 B 9/1/2019 10/31/2019 \$     549.80 B 11/1/2019 12/31/2019 \$     560.00 B 1/1/2020 9/30/2020 \$     549.80

Desired result:

 Product ValidFrom ValidTo Price PrevPrice Price Diff A 1/1/2019 2/28/2019 \$     992.30 A 3/1/2019 7/10/2019 \$     984.20 \$     992.30 =984.20-992.30 = -8.10 A 7/11/2019 8/31/2019 \$  1,002.50 \$     984.20 \$     18.30 A 9/1/2019 10/31/2019 \$     986.20 \$  1,002.50 \$    (16.30) B 1/1/2019 8/31/2019 \$     570.20 B 9/1/2019 10/31/2019 \$     549.80 \$     570.20 =549.80-570.20= \$    (20.40) B 11/1/2019 12/31/2019 \$     560.00 \$     549.80 \$     10.20 B 1/1/2020 9/30/2020 \$     549.80 \$     560.00 \$    (10.20)

Note : There are multiple products with different date ranges (Validfrom - ValidTo), it's not consistent.

Trials : Created calendar date table & used active, inactive relationships with fact table on ValidFrom, ValidTo dates. But not succeeded with writing dax calculation.

Tried to create index columns, starting from 0 & 1. But doesn't work as it's not continuous and changes for each product.

Tried to create two separate date tables for ValidFrom & ValidTo dates, unsuccesful.

Please help me with your suggestions on how to proceed and what would be the best approach to calculate this price difference either with DAX/Power Query without any performance issues as we will be dealing with large dataset here.

1 ACCEPTED SOLUTION
Community Support

You can try the following methods.

Measure:

``````PrevPrice =
VAR PrevDate =
MAXX (
FILTER (
ALL ( 'Table'[ValidFrom], 'Table'[Product ID] ),
'Table'[ValidFrom] < SELECTEDVALUE ( 'Table'[ValidFrom] )
&& [Product ID] = SELECTEDVALUE ( 'Table'[Product ID] )
),
'Table'[ValidFrom]
)
VAR PrevPrice =
CALCULATE (
SUM ( 'Table'[Price] ),
FILTER (
ALL ( 'Table' ),
[ValidFrom] = PrevDate
&& [Product ID] = SELECTEDVALUE ( 'Table'[Product ID] )
)
)
RETURN
PrevPrice``````
``Price Diff = IF([PrevPrice]<>BLANK(),SUM('Table'[Price])-[PrevPrice])``

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

You can try the following methods.

Measure:

``````PrevPrice =
VAR PrevDate =
MAXX (
FILTER (
ALL ( 'Table'[ValidFrom], 'Table'[Product ID] ),
'Table'[ValidFrom] < SELECTEDVALUE ( 'Table'[ValidFrom] )
&& [Product ID] = SELECTEDVALUE ( 'Table'[Product ID] )
),
'Table'[ValidFrom]
)
VAR PrevPrice =
CALCULATE (
SUM ( 'Table'[Price] ),
FILTER (
ALL ( 'Table' ),
[ValidFrom] = PrevDate
&& [Product ID] = SELECTEDVALUE ( 'Table'[Product ID] )
)
)
RETURN
PrevPrice``````
``Price Diff = IF([PrevPrice]<>BLANK(),SUM('Table'[Price])-[PrevPrice])``

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

You could create a couple of calculated columns like

``````Prev price =
VAR currentValidFrom = 'Table'[Valid from]
RETURN
SELECTCOLUMNS (
CALCULATETABLE (
TOPN ( 1, 'Table', 'Table'[Valid from] ),
ALLEXCEPT ( 'Table', 'Table'[Product] ),
'Table'[Valid from] < currentValidFrom
),
"@val", 'Table'[Price]
)

Price diff = 'Table'[Price] - 'Table'[Prev price]``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors