Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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())
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |