Row Difference (Using 2 Index Columns)

[Anonymous]

Good Afternoon All,

Ive been going around in circles with this for a while, i appreciate it may seem easy but i just cant get it to work.

I have a table with information from our production line, it basically keeps a running count of our production counter every hour. I can see the values as shown below & I know if I subract the container count associated with SIC_ID 1 from SIC_ID 2 I get 30,002. But how do i do this in power BI automatically?

Currently there are different lines recorded to this table & the SIC_ID is 1 to 24 then resets & starts over again. Im assuming I need to filter on Line & ID, Any suggestions?

Thanks,

Ash

[HotChilli · Accepted solution]

There's a pattern for getting the previous value ( this should work for your initial scenario, you might need to change it for the full scenario)

Previous Val Column = VAR _line = ProdLine[line]
              VAR _sic = ProdLine[sic_id]
RETURN
    CALCULATE(SUM(ProdLine[con]), 
        FILTER(ProdLine,
            ProdLine[line] = _line && 
            ProdLine[sic_id] =  _sic -1))

You then have the previous value on the same line as the current value so you can add a column to subtract one from the other

[Anonymous]

You're a star! thankyou