cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors