Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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" )
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:
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.
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.
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".
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. 🙂
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.
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" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |