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

[TJ19]

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!

[HarishKM]

@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 .

Kindly follow above image for your reference.

Thanks
Harish M

[TJ19]

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]

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