Power Query Next row based on filter

[LuciferMstar]

Hey people,

I have tried to translate the following Alteryx formula, and I need to apply the same logic in Power BI: 

IIF([ID]=[Row+1:ID]&&[STATUS]="On Hold", datetimediff([Row+1:CREATED DATE],[CREATED DATE],"days"), 0)

I need your help as I can't understand why it doesn't work.  I'm not sure if it works in DAX, but from what I read it doesn't. 

Basically I need to get the number of days for each ID between a date and status and the next date after that status. 

I've tried using the Index methods and other Solved cases, but I can't seem to make it to work. 

In the following table I'm getting the result as Days, the difference in days between the On Hold Status and its Created Date and the next Status that is not On Hold and its created date. 

ID	Status	Created Date	Days
226	Open	04/07/2018	0
226	On Hold	25/07/2018	9
226	Open	03/08/2018	0
226	Open	09/08/2018	0
226	On Hold	09/08/2018	61
226	On Hold	08/10/2018	1
226	Open	09/10/2018	0
226	On Hold	15/10/2018	3
226	Closed	18/10/2018	0

Many thanks!

[wdx223_Daniel]

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

[LuciferMstar]

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!

[wdx223_Daniel]

for DAX code

what means "until end"

could you simulate some sample data and the desired output?

[LuciferMstar]

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

[wdx223_Daniel · Accepted solution]

=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

[LuciferMstar]

@wdx223_Daniel 

Perfect, it works. Thank you!

[wdx223_Daniel]

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
    Custom1

the M code is ok on my PC