cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors