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 t...
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
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 ??

 

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi nishanthapbi

 

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

  • nishanthapbi's avatar
    nishanthapbi
    Regular Visitor
    8 years ago

    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's avatar
      Phil_Seamark
      Microsoft Employee
      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