Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a Status_History table linked by ORDERID. The Status_History table contains the Date and Status for each change.
I want to create a measure that calculates the number of days in a Status from changed date as shown in the table.
StatusHistorymight look like this:
Created Date | Order ID | Changed Date | Status | No of Days in a Status |
01/01/2023 | AA | 04/01/2023 | New | 3 |
01/01/2023 | AA | 06/01/2023 | In Progress | 2 |
01/01/2023 | AA | 07/01/2023 | OnHold | 1 |
01/01/2023 | AA | 11/01/2023 | Diagnostic | 4 |
01/01/2023 | AA | 12/01/2023 | Dispatch | 1 |
21/01/2023 | AB | 06/02/2023 | New | 16 |
21/01/2023 | AB | 13/02/2023 | In Progress | 7 |
21/01/2023 | AB | 14/02/2023 | OnHold | 1 |
21/01/2023 | AB | 17/02/2023 | Diagnostic | 3 |
21/01/2023 | AB | 18/02/2023 | Dispatch | 1 |
10/02/2023 | AD | 13/03/2023 | New | 31 |
10/02/2023 | AD | 16/03/2023 | In Progress | 3 |
10/02/2023 | AD | 18/03/2023 | OnHold | 2 |
10/02/2023 | AD | 20/03/2023 | Diagnostic | 2 |
10/02/2023 | AD | 21/03/2023 | Dispatch | 1 |
02/03/2023 | AC | 12/04/2023 | New | 41 |
02/03/2023 | AC | 13/04/2023 | In Progress | 1 |
02/03/2023 | AC | 15/04/2023 | OnHold | 2 |
02/03/2023 | AC | 25/04/2023 | Diagnostic | 10 |
02/03/2023 | AC | 02/05/2023 | Dispatch | 7 |
22/03/2023 | AV | 23/05/2023 | New | 62 |
22/03/2023 | AV | 08/06/2023 | In Progress | 16 |
Ultimately, I'd like to get the average no of days in a status by month.
Any help will be greatly appreciated
Thanks!
@TJ19 Hello ,
Kindly create a column in table.
Then used this dax .
Kindly follow above image for your reference.
Thanks
Harish M
Hi Harish,
Thank you for reply.
I would need the no of days in each status from the changed date to changed date but not in Today() date.
AA has been in Inprogress Status from 04/01/2023 till 06/01/2023 which is 2 days
and then Onhold status from 04/01/2023 till 07/01/2023 which is 1 days
Thanks,
TJ
@Vera_33 Can you please help. I saw your reply for a similar question but that didn't work for me
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
94 | |
92 | |
83 | |
69 |
User | Count |
---|---|
162 | |
129 | |
126 | |
106 | |
98 |