Subtracting values in a column by prior Date filtered by another column

[kozlevchar]

Hello PBI Community,

I have what I thought might be a common problem.  

I need to subtract the cost column by the prior date filtered by the Material number.  Here's the table:

Date	Material	Cost
3/1/2022	11223344	10,000
3/1/2022	12345678	30,000
3/1/2022	23456789	25,000
3/1/2022	34567891	50,000
3/1/2022	45678912	35,000
4/1/2022	11223344	20,000
4/1/2022	12345678	35,000
4/1/2022	23456789	20,000
4/1/2022	34567891	60,000
4/1/2022	45678912	25,000

For example, the difference in cost for Material 11223344 would be +10,000 (20,000 - 10,000).

I am able to create separate Measures for current cost (using MAX function) and previous cost (using PREVIOUSMONTH) then subtracting the measures but was hoping to create a measure where I can subtract any month w/o having to create separate measures.

Any help would be much appreciated!

Thank you,

John 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Cost Diff measure: = 
VAR _currentcost =
    SUM ( Data[Cost] )
VAR _previousmonthcost =
    CALCULATE ( SUM ( Data[Cost] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
    IF (
        _currentcost = BLANK ()
            || _previousmonthcost = BLANK (),
        " ",
        _currentcost - _previousmonthcost
    )

[kozlevchar]

Thanks so much.  Very appreciated!

John