Calculate number of days in period excluding during MCO Period Start MCO and End MCO

[Anonymous]

Hi all

I'm struggling with a way to calculate the number of days excluding the MCO period.  If the incurred date and Resolved date is between the MCO period how many days are there in that period.
The cycle time exclude the MCO is the days that counted exluding the period of MCO

Anyone can help me on this ?
here is the attach for your reference.

Thanks

MCO Start	01/06/2021
MCO End	01/09/2021
Last date before MCO	31/05/2021
Status	Cycle Time	Incurred	Resolved	Days in MCO 3.0	Cycle Time Exclude MCO 3.0
Ticket Closed	55	23/04/2021	16/06/2021	16	39
Ticket Closed	119	4/23/2021	8/19/2021	80	39
Ticket Closed	119	4/23/2021	8/19/2021	80	39
Ticket Closed	119	4/23/2021	8/19/2021	80	39

[Anonymous · Accepted solution]

Hi @Anonymous ,

Not very clear about this——how can i calculate using the queries for Days in MCO without hardcode in excel

For your original post, I have done it in Power BI using DAX.

Firstly, as @johnt75 mentioned , you may need a Date table for slicers/filters to set the Start and End date for MCO status. For example:

MCO Dates = CALENDAR(DATE(2021,1,1),DATE(2021,12,31)) 

Then create measures:

Cycle Time = DATEDIFF(MAX('Table'[Incurred]),MAX('Table'[Resolved]),DAY)+1

Days in MCO = 
var _start=IF(MIN('MCO Dates'[Date])>MAX('Table'[Incurred]),MIN('MCO Dates'[Date]),MAX('Table'[Incurred]))
var _end=IF(MAX('MCO Dates'[Date])<MAX('Table'[Resolved]),MAX('MCO Dates'[Date]),MAX('Table'[Resolved]))
return DATEDIFF(_start,_end,DAY)+1

Cycle Time Exclude MCO = [Cycle Time] -[Days in MCO] 

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Assuming that you have a date table and slicer tables for the MCO start & end dates, you could create a measure like

Cycle Time excluding MCO =
var MCOStartDate = SELECTEDVALUE( 'MCO Start Date'[Date])
var MCOEndDate = SELECTEDVALUE( 'MCO End Date'[Date])
var datesToCount = EXCEPT( 
   DATESBETWEEN( 'Date'[Date], SELECTEDVALUE('Table'[Incurred]), SELECTEDVALUE('Table'[Resolved]),
   DATESBETWEEN('Date'[Date], MCOStartDate, MCOEndDate)
)
return COUNTROWS(datesToCount)

[Anonymous]

for 

how can i calculate using the queries for Days in MCO without hardcode in excel