DAX calculation

[nishanthapbi]

I need to create calculated column to get the consumption (kg) using a formula in PBI. Here I have the flow rate (Kg/min) and need to get the time from subtracting the previous time which is in the same Item and same day (because time gap of the two consecutive data is different). With this calculation fist raw of the Item will get 0 as the time is become 0 and others will get the respective amount corresponding to the time. 

**Consumption (Kg) = Flow (Kg/min)/60 * time (seconds) 

[Phil_Seamark · Accepted solution]

HI @nishanthapbi

This calculated column gets pretty close.  It shouldn't require too much tweaking. 

Consumption (Kg) = 
VAR Yellow = 'Table1'[Time]
VAR Red = MAXX(
            FILTER(
                'Table1',
                'Table1'[Date] = EARLIER('Table1'[Date]) && 
                'Table1'[Item] = EARLIER('Table1'[Item]) && 
                'Table1'[Time] < EARLIER('Table1'[Time])
                ),'Table1'[Time])
VAR TimeGap = DATEDIFF(Red,Yellow,SECOND)          
VAR Flow = 'Table1'[Flow (Kg/min)]
RETURN 
    (Flow/60) * TimeGap

[Phil_Seamark · Accepted solution]

HI @nishanthapbi

This calculated column gets pretty close.  It shouldn't require too much tweaking. 

Consumption (Kg) = 
VAR Yellow = 'Table1'[Time]
VAR Red = MAXX(
            FILTER(
                'Table1',
                'Table1'[Date] = EARLIER('Table1'[Date]) && 
                'Table1'[Item] = EARLIER('Table1'[Item]) && 
                'Table1'[Time] < EARLIER('Table1'[Time])
                ),'Table1'[Time])
VAR TimeGap = DATEDIFF(Red,Yellow,SECOND)          
VAR Flow = 'Table1'[Flow (Kg/min)]
RETURN 
    (Flow/60) * TimeGap

[nishanthapbi]

Thank you very much for the help, if need to multiply the time gap with the previous flow rate how it's posible ??

[Phil_Seamark]

Hi @nishanthapbi

Do you mean 2 back?  The calc I posted looks back 1 time period

[nishanthapbi]

Time gap calculation is correct, I mean that time gap need to multiply with the previous flow rate to take the consumption.

Eg: consumption (kg) = flow (Kg/min) (Green Color) / 60 * Time (Yellow - Red)

[Phil_Seamark · Accepted solution]

Hi @nishanthapbi

I've tweaked the calculation and highlighted the changes in bold.

Consumption (Kg) = 
VAR Yellow = 'Table1'[Time]
VAR Red = MAXX(
            FILTER(
                'Table1',
                'Table1'[Date] = EARLIER('Table1'[Date]) && 
                'Table1'[Item] = EARLIER('Table1'[Item]) && 
                'Table1'[Time] < EARLIER('Table1'[Time])
                ),'Table1'[Time])
VAR TimeGap = DATEDIFF(Red,Yellow,SECOND)          
VAR Flow = 'Table1'[Flow (Kg/min)]
VAR PreviousFlow = MAXX(FIlTER('Table1',Table1[Item] = EARLIER('Table1'[Item]) && 'Table1'[Date] = EARLIER('Table1'[Date]) && 'Table1'[Time] =  Red),'Table1'[Flow (Kg/Min)])RETURN 
    (PreviousFlow/60) * TimeGap