Forum Discussion

cong_nguyen_acc's avatar
cong_nguyen_acc
Frequent Visitor
6 years ago

Calculation price variance over time

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. ...
Need Help
v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

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.