cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

Measure to Calculate the number of days between different Status by ID

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!

3 REPLIES 3
Impactful Individual

@TJ19  Hello ,
Kindly create a column in table.

Then used this dax .

No of days- = TODAY() - Sheet1[Date]

This will give you the number of days then you have to select the no of days column then change the summarization status to do not summarize .

Thanks
Harish M

Frequent Visitor

Hi Harish,

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

Frequent Visitor

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors