Forum Discussion
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")
part | install date | started |
| PAU-0101 | 26/03/2019 05:17 | yes |
| PAU-0101 | 07/04/2019 18:56 | No |
| PAU-0101 | 07/04/2019 19:56 | yes |
| PAU-0102 | 26/03/2019 05:17 | yes |
| PAU-0102 | 07/04/2019 18:56 | No |
| PAU-0102 | 07/04/2019 19:56 | yes |
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" )
10 Replies
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.
Thank you, elegant coding.
I tried your suggestion but it does not look right, see my results table: