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.
Good day!
Data as below.
I want to calculate, for each Project, the difference in days from a "Case Open" (false to true) to File Status" Rejected.
In the example, Project KCG’s calculation is (using the Edit Date column): 29/07/2021 - 28/07/2021 = 1 day
In PowerBI, what is the good way to do so? Would there be a way to restructure the data, a formula’s better?
(note: the data has hundreds of rows, that not each Project has the row of "Case Open" (false to true) and File Status" Rejected.)
Thank you.
Project | Origin | Staff | Created Date | Edit Date | Milestone | Value before | Value after |
GIN | David | Mike | 04/09/2021 | 04/12/2021 | Case Open | false | true |
GIN | David | Mike | 04/09/2021 | 04/12/2021 | Completed | ||
KCG | Peter | Jordon | 07/06/2021 | 28/07/2021 | Case Open | false | true |
KCG | Peter | Jordon | 07/06/2021 | 29/07/2021 | File Status | New | Rejected |
PQE | Lilly | MJ | 07/08/2021 | 19/07/2021 | File Status | New | Accepted |
Solved! Go to Solution.
@JohnnyK , Create a new column like
new column =
var _1 = maxx(filter(Table, [Edit Date] <earlier([Edit Date]) && [Project] =earlier([Project]) && [Milestone] ="Case Open"),[Edit Date])
return
if(not(isblank(_1)) && [Value after] ="Rejected", datediff(_1, [Edit Date],day), blank())
@JohnnyK , Create a new column like
new column =
var _1 = maxx(filter(Table, [Edit Date] <earlier([Edit Date]) && [Project] =earlier([Project]) && [Milestone] ="Case Open"),[Edit Date])
return
if(not(isblank(_1)) && [Value after] ="Rejected", datediff(_1, [Edit Date],day), blank())
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |