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" )

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.

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

    Hi!! Thanks a lot!! Almost there!!!

    See that there are a few rows (highlighted) where the response should be "No", because the difference between rows 2 and 1 is less than 1 day (24 hours) for the "Installdate".

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi edgarjb-68 ,

      This should work:

      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], HOUR ) < 24 ), "No", "Yes" )

      Thanks.

      Please accept this as a solution if it satisfies the requirement. Appreciate your Kudos. :)

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

        Wow, it is very, very close to it!!!

        Though I still find only 5 instances where the response should be "No", see two examples highlighted.

        One thing I noticed is that on those cases the "installdate" difference is very close to 24 hours, somthing like 23.5 or higher.