Forum Discussion
Power Query Next row based on filter
=VAR _n=MINX(FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Created Date]>EARLIER(Data[Created Date])),Data[Created Date]) VAR _wo=COUNTROWS(FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Created Date]<EARLIER(Data[Created Date])&&'Data'[Status]="WO")) RETURN IF(_n<>BLANK()&&Data[Status]="On Hold"&&_wo=0,DATEDIFF(Data[Created Date],_n,DAY),0)
can this work in DAX
DAX CalculatedColumn=VAR _n=MINX(FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Created Date]>EARLIER(Data[Created Date])),Data[Created Date]) RETURN IF(_n<>BLANK()&&Data[Status]="On Hold",DATEDIFF(Data[Created Date],_n,DAY),0)
M Code:
NewStep= let a=Table.ToRows(YourTableName),b=List.Skip(a)&{List.Repeat({null},Table.ColumnCount(YourTableName))} in #table(Table.ColumnNames(YourTableName)&{"Days"},List.Transform(List.Zip({a,b}),each _{0}&{if _{0}{0}=_{1}{0} and _{0}{1}="On Hold" then Duration.Days(_{1}{2}-_{0}{2}) else 0}))
Hi wdx223_Daniel ,
The DAX works, thank you!
The M Code just gives an error, something with cyclic.
But I forgot to add that there are some cases where I have "On Hold" after "WO" status by Created Date and in this form it keeps calculating until the end. How do I set a condition that if "On Hold"&&"Created Date" > "WO"&&"Created Date" to do nothing, or ignore it, etc.
Many thanks!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIyU9JR8i9IzQNSRgaGFroG5roGJkqxOnC5PAWP/JwUJGkjU2RpZK1AZIxHzhKPsfilDQ2AKnCYDJLDr9UQ2cHOOfnFqSiyQINjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, #"Created Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created Date", type date}, {"ID", Int64.Type}}), Custom1 = let a=Table.ToRows(#"Changed Type"),b=List.Skip(a)&{List.Repeat({null},Table.ColumnCount(#"Changed Type"))} in #table(Table.ColumnNames(#"Changed Type")&{"Days"},List.Transform(List.Zip({a,b}),each _{0}&{if _{0}{0}=_{1}{0} and _{0}{1}="On Hold" then Duration.Days(_{1}{2}-_{0}{2}) else 0})) in Custom1the M code is ok on my PC
for DAX code
what means "until end"
could you simulate some sample data and the desired output?
Hi wdx223_Daniel ,
Thank you for the help. Basically it should calculate the days until WO if it exists. If WO is not present it should calculate as it does now, until the next status, whatever that is.
Looking at the previous table, it would be something like this:
ID Status Created Date Days 226 Open 09/08/2018 0 226 On Hold 09/08/2018 61 226 On Hold 08/10/2018 1 226 WO 09/10/2018 0 226 Open 09/10/2018 0 226 On Hold 15/10/2018 0 226 Closed 18/10/2018 0 226 On Hold 19/10/2018 0 =VAR _n=MINX(FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Created Date]>EARLIER(Data[Created Date])),Data[Created Date]) VAR _wo=COUNTROWS(FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Created Date]<EARLIER(Data[Created Date])&&'Data'[Status]="WO")) RETURN IF(_n<>BLANK()&&Data[Status]="On Hold"&&_wo=0,DATEDIFF(Data[Created Date],_n,DAY),0)
can this work in DAX