Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
Solved! Go to 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
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!
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
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |