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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors