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.
Hi, I am working on a scenario where an Application/Case is filed into a system and its journey is also tracked as the application/case progresses to further stages. I need to find how much time an application/case spends in each stage bucket. Below are my master and transaction table :
Below is the expected output :
Can someone help me how can I create above calculated table which can help me govern how much time is spent in each bucket.
@GilbertQ
Solved! Go to Solution.
@Anonymous
You can create a column in the Application History table.
Time(days) = IF([Prev] = "Stage 1",
DATEDIFF(RELATED('Application Master'[Created On]),'Application History'[updated on],day),
DATEDIFF(CALCULATE(MAX([Updated On]),FILTER('Application History',[Updated On]<EARLIER([Updated On]))),[Updated On],DAY))
If need a calculated table, you can summarize():
Table = SUMMARIZE('Application History','Application History'[Application ID],[Prev],'Application History'[Time(days)])
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
You can create a column in the Application History table.
Time(days) = IF([Prev] = "Stage 1",
DATEDIFF(RELATED('Application Master'[Created On]),'Application History'[updated on],day),
DATEDIFF(CALCULATE(MAX([Updated On]),FILTER('Application History',[Updated On]<EARLIER([Updated On]))),[Updated On],DAY))
If need a calculated table, you can summarize():
Table = SUMMARIZE('Application History','Application History'[Application ID],[Prev],'Application History'[Time(days)])
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @V-pazhen-msft thank you for your DAX, it is working in majority cases, for rest I have some data anomalies. Can you please help me tweak your DAX to find time difference for below scenarios.
For each Application ID, find TAT between Stage 1 to Stage 4, Stage 2 to Stage 4 and Stage 3 to Stage 4.
Here is what I am trying :
@Anonymous , a new column in Application history
New column = datediff(maxx(filter(Applicationmaster ,Applicationmaster[Application ID] =Applicationhistory[Application ID]),Applicationmaster[Created on]),Applicationhistory[updated on],day)
New column = datediff(maxx(filter(Applicationmaster ,Applicationmaster[Application ID] =Applicationhistory[Application ID]),Applicationmaster[Created on]),Applicationhistory[updated on],day) & "Day"
Thanks a lot @amitchandak for your quick answer. Your DAX also gives me datediff from stage 1 to stage 2, stage 1 to stage 3 and stage 1 to stage 4. Apologies couldn't reply earlier as I was unavailable.
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |