Skip to main content
cancel
Showing results for 
Search instead 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

Reply
TJ19
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 DateOrder IDChanged DateStatusNo of Days in a Status
01/01/2023AA04/01/2023New3
01/01/2023AA06/01/2023In Progress2
01/01/2023AA07/01/2023OnHold1
01/01/2023AA11/01/2023Diagnostic4
01/01/2023AA12/01/2023Dispatch1
21/01/2023AB06/02/2023New16
21/01/2023AB13/02/2023In Progress7
21/01/2023AB14/02/2023OnHold1
21/01/2023AB17/02/2023Diagnostic3
21/01/2023AB18/02/2023Dispatch1
10/02/2023AD13/03/2023New31
10/02/2023AD16/03/2023In Progress3
10/02/2023AD18/03/2023OnHold2
10/02/2023AD20/03/2023Diagnostic2
10/02/2023AD21/03/2023Dispatch1
02/03/2023AC12/04/2023New41
02/03/2023AC13/04/2023In Progress1
02/03/2023AC15/04/2023OnHold2
02/03/2023AC25/04/2023Diagnostic10
02/03/2023AC02/05/2023Dispatch7
22/03/2023AV23/05/2023New62
22/03/2023AV08/06/2023In Progress16

 

 

 

 

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
HarishKM
Impactful Individual
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 .

HarishKM_0-1695384804125.png



Kindly follow above image for your reference.

Thanks
Harish M

TJ19
Frequent Visitor

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

TJ19
Frequent Visitor

@Vera_33 Can you please help. I saw your reply for a similar question but that didn't work for me

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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