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!! 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. :)

  • Anonymous's avatar
    Anonymous
    Not applicable
    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
    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.

     

  • edgarjb-68's avatar
    edgarjb-68
    Frequent Visitor
    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" )