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

[Madhavthota]

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.

Thanks in advance and I really appreciate your help.

 

[v-zhangti · Accepted solution]

Hi, @Madhavthota 

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.

[johnt75]

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]