Forum Discussion

nishanthapbi's avatar
nishanthapbi
Regular Visitor
8 years ago
Solved

DAX calculation

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) 

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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's avatar
    Phil_Seamark
    8 years ago

    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

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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's avatar
      nishanthapbi
      Regular Visitor
      8 years ago

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