Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.