Forum Discussion

edgarjb-68's avatar
edgarjb-68
Frequent Visitor
6 years ago
Solved

Row calculation

How to calculate rows on a table in Power BI? I want to create a column "started" as below, for each row of this column I need the result like this excel formula: =IF(AND(A3=A2;B3-B2<1);"No";"yes") ...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago
    Hi ,
    Can you try to change the datediff to minutes and change the less than condition.
    Thanks.
  • edgarjb-68's avatar
    edgarjb-68
    6 years ago

    JUST PERFECT!!!

     

    started = VAR v_LagPart = CALCULATE( MAX( Data[part] ), FILTER( Data, Data[part] = EARLIER( Data[part] ) && Data[installdate] < EARLIER( Data[installdate] ) ) ) VAR v_LagDate = CALCULATE( MAX( Data[installdate] ), FILTER( Data, Data[part] = EARLIER( Data[part] ) && Data[installdate] < EARLIER( Data[installdate] ) ) ) RETURN IF( Data[part] = v_LagPart && ( DATEDIFF( v_LagDate, Data[installdate], MINUTE ) < 1440 ), "No", "Yes" )

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi,

Can you try to use this:

started = 
VAR v_LagPart = CALCULATE( MAX( Data[part] ),
          FILTER( Data, Data[part] = EARLIER( Data[part] ) &&
          Data[installdate] < EARLIER( Data[installdate] )
          )
) 

VAR v_LagDate = CALCULATE( MAX( Data[installdate] ),
          FILTER( Data, Data[part] = EARLIER( Data[part] ) &&
          Data[installdate] < EARLIER( Data[installdate] )
          )
)
RETURN IF( Data[part] = v_LagPart && ( DATEDIFF( Data[installdate],v_LagDate, DAY ) < 1 && DATEDIFF( Data[installdate],v_LagDate, DAY ) <> 0 ), "No", "Yes" )

Thanks.

edgarjb-68's avatar
edgarjb-68
Frequent Visitor
6 years ago

Thank you, elegant coding.

I tried your suggestion but it does not look right, see my results table:

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi,
    Could you please explain the if condition. I mean what are you trying to achieve using it, specifically the date comparison part.
    Thanks.
    • edgarjb-68's avatar
      edgarjb-68
      Frequent Visitor
      6 years ago

      Hi, thanks a lot for the help so far!! 

      First the table columns must be sorted, Column "Part" ascending and column "Installdate-time" also  ascending.

      the column "started" intents to return a "no" if:

              the "Part" in row 2 is equal to the "Part" in row 1, and

              if the "Installdate-time" of row 2 minus "Installdate-time" of row 1 is less than 1 day.

             But, if the difference between the row 2 and row 1 "Installdate-time" is more than 1 day, it must return a "yes".

       

      So for the same "Part", if it runs less than one day it did not started, but if it runs more than one day it is considered as successfully started.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi edgarjb-68 ,

        Can you try using this:

        started = 
VAR v_LagPart = CALCULATE( MAX( Data[part] ),
          FILTER( Data, Data[part] = EARLIER( Data[part] ) &&
          Data[installdate] < EARLIER( Data[installdate] )
          )
) 

VAR v_LagDate = CALCULATE( MAX( Data[installdate] ),
          FILTER( Data, Data[part] = EARLIER( Data[part] ) &&
          Data[installdate] < EARLIER( Data[installdate] )
          )
)
RETURN IF( Data[part] = v_LagPart && ( DATEDIFF( v_LagDate, Data[installdate], DAY ) < 1 ), "No", "Yes" )

        Thanks.