Calculation price variance over time

[cong_nguyen_acc]

Hi,

I need help solving a calculation like this :

+ The table below shows the purchase price of an item from times to times.

+ A date mays have many prices , distincted by increased record_ID.

+ An item mays have many currency codes.

+ the dates aren't continuous.

the goal is calculation the price variance with previous price , correspoding with currency code.

Pls help me with this. 

Thanks & best regards,

Record_ID SKU_ID Invoice_Date Invoice_Datetime Purchase_Price Currency_Code

5637227309	FO01	15/05/2019	15/05/2019 9:38	5.68	USD
5637227316	FO01	16/05/2019	15/05/2019 10:02	5.05	USD
5637227328	FO01	16/05/2019	16/05/2019 9:09	6.68	USD
5637227329	FO01	16/05/2019	16/05/2019 9:10	5.68	USD
5637227376	FO01	17/05/2019	17/05/2019 7:00	5.68	USD
5637234115	FO01	26/06/2019	26/06/2019 9:42	5.08	USD
5637234804	FO01	28/06/2019	28/06/2019 9:26	5.08	USD
5637234805	FO01	28/06/2019	28/06/2019 9:39	5.08	USD
5637237067	FO01	22/07/2019	22/07/2019 2:50	7.08	USD
5637255524	FO01	23/10/2019	25/10/2019 6:05	15.00	SGD

[v-zhenbw-msft]

Hi @cong_nguyen_acc ,

We test the nandukrishnavs’ code and it works fine.

Maybe you can try the following measure.

Measure = 
VAR current_time =
    MIN ( 'Table'[Invoice_Datetime] )
VAR _previousDateTime =
    CALCULATE (
        MAX ( 'Table'[Invoice_Datetime] ),
        ALLSELECTED ( 'Table' ),
        'Table'[SKU_ID] IN DISTINCT ( 'Table'[SKU_ID] ),
        'Table'[Invoice_Datetime] < current_time,
        'Table'[Currency_Code] IN DISTINCT ( 'Table'[Currency_Code] )
    )
VAR _previousPrice =
    CALCULATE (
        SUM ( 'Table'[Purchase_Price] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Invoice_Datetime] = _previousDateTime
    )
RETURN
    IF (
        ISBLANK ( _previousPrice ),
        BLANK (),
        SUM ( 'Table'[Purchase_Price] ) - _previousPrice
)

The result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[amitchandak]

@cong_nguyen_acc , You can get last date price like. Use date table

A new measure

Last Day Non Continous Price = CALCULATE(Avg('Table'[Price]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))

 And take diff with price

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[nandukrishnavs]

@cong_nguyen_acc 

Variation = 
var _currencu='Table'[Currency_Code]
var _previousDateTime=MAXX(FILTER(ALL('Table'),'Table'[SKU_ID]=EARLIER('Table'[SKU_ID])&&'Table'[Invoice_Datetime ]<EARLIER('Table'[Invoice_Datetime ])&&'Table'[Currency_Code]=_currencu),'Table'[Invoice_Datetime ])
var _previousPrice= MAXX(FILTER(ALL('Table'),'Table'[Invoice_Datetime ]=_previousDateTime),'Table'[Purchase_Price ])
var _variation= 'Table'[Purchase_Price ]-_previousPrice
return IF(ISBLANK(_previousPrice),BLANK(),_variation)

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[cong_nguyen_acc]

Thanks nandukrishnavs,

Your code works but it still have glitch :

Variation goes wrong when i select this item:

+ Line 5637171193 , the expected result should be 0 .

+ Line 5637177070 should be 35.

+ Line 5637182299 should be 0.

+ Line 5637250795 should be 0.

Pls check you code with this data sample.

Thanks.

Record_ID	SKU_ID	Invoice_Date	Invoice_Datetime	M_Previous_Date	M_Min_Date	Purchase_Price	Currency_Code	variation
5637165016	01VCDB5TP	29/05/2017 0:00	30/05/2017 1:55	29/05/2017 0:00	29/05/2017 0:00	30	USD
5637171193	01VCDB5TP	06/09/2017 0:00	11/09/2017 6:53	06/09/2017 0:00	06/09/2017 0:00	30	USD	-15
5637177070	01VCDB5TP	22/11/2017 0:00	22/11/2017 9:18	22/11/2017 0:00	22/11/2017 0:00	65	USD	-30
5637177071	01VCDB5TP	22/11/2017 0:00	22/11/2017 9:20	22/11/2017 0:00	22/11/2017 0:00	65	USD
5637182299	01VCDB5TP	29/01/2018 0:00	30/01/2018 3:39	29/01/2018 0:00	29/01/2018 0:00	65	USD	-189
5637232630	01VCDB5TP	12/06/2019 0:00	13/06/2019 10:36	12/06/2019 0:00	12/06/2019 0:00	39	USD	-26
5637250795	01VCDB5TP	13/09/2019 0:00	13/09/2019 7:29	13/09/2019 0:00	13/09/2019 0:00	39	USD	-31
5637252349	01VCDB5TP	27/09/2019 0:00	27/09/2019 7:20	27/09/2019 0:00	27/09/2019 0:00	39	USD
5637266340	01VCDB5TP	24/02/2020 0:00	24/02/2020 10:57	24/02/2020 0:00	24/02/2020 0:00	39	USD
5637270069	01VCDB5TP	08/04/2020 0:00	09/04/2020 8:20	08/04/2020 0:00	08/04/2020 0:00	39	USD

[AllisonKennedy]

If you are wanting the price it was sold at most recently, you'll need to use EARLIER function inside a calculated column. If you want the price from a previous day or month, then you need to create a continuous date table, relate it to your existing table and use time intelligence. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[AllisonKennedy]

Further to my previous reply, here is a sample formula that should help: 

Previous Purchase Price = MAXX(FILTER(Items, Items[SKU_ID]=EARLIER(Items[SKU_ID])&&Items[Currency_Code]=EARLIER(Items[Currency_Code])&& Items[Invoice_DateTime]<EARLIER(Items[Invoice_DateTime])),Items[Purchase_Price])

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv