How many days open in a given time frame

[LeeGrimshaw1]

I am trying to create a measure that calculates how many days a ticket has been open in a given period of time. So if the period I am interested in is between 1st Jan - 4th Jan and the ticket was open between 2nd Jan - 5th Jan the measure would return 3 as the ticket was open for 4 days but the last day was in the next period.

That is my first problem! The second is that tickets can't always be worked every day. So I need to discount the days it can't be worked from the total number of days the measure returns. So in the example I used above, if the ticket was not able to be worked on the 3rd Jan, the total would now reduce to 2.

The data would come from two tables as per picture 1. The result for if the measure was looking at period 2 would be the table in picture 2. A colleague of mine suggested this might be easier in PQ but I have no idea how to achieve that.

Any help greatly appreciated.

[Anonymous]

Hi @LeeGrimshaw1 

Did the responses from DataNinja777 and bhanu_gautam assist you in resolving your problem? If resolved, please accept their responses as the solution. Furthermore, please allow me to add my solution here.

1. Create a measure with the following DAX:

Number of Days Open that could have been worked = 
VAR SlicerStartDate = MIN('WorkDay'[Date])
VAR SlicerEndDate = MAX('WorkDay'[Date])
VAR TicketStartDate = SELECTEDVALUE('Ticket'[Ticket Start Date])
VAR TicketEndDate = SELECTEDVALUE('Ticket'[Ticket End Date])
VAR TicketActualStartDate = IF(TicketStartDate < SlicerStartDate, SlicerStartDate, TicketStartDate)
VAR TicketActualEndDate = IF(TicketEndDate > SlicerEndDate, SlicerEndDate, TicketEndDate)
VAR WorkDays =
CALCULATE(
    COUNTROWS('WorkDay'),
    'WorkDay'[Date] >= TicketActualStartDate,
    'WorkDay'[Date] <= TicketActualEndDate,
    'WorkDay'[Available to be worked?] = "Available"
)
RETURN
IF(TicketStartDate > SlicerEndDate || TicketEndDate < SlicerStartDate, 0, WorkDays)

2. Create a slicer with "Date" cloumn of table "Workday", and select "Between" style in Slicer settings.

3. Here is my test result, please refer to the uploaded pbix file for details.

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

[LeeGrimshaw1 · Accepted solution]

Thank you for your attempt at solving my problem!

In the end I found a solution by using Power Query to achieve the required result. I created a list of dates in a custom collumn which contained the dates between the start and ned dates. Once expanded, this new collumn contains all the dates the ticket was open for. Once at this point I was able to merge another table to add the detail of whether or not the ticket was able to be worked on a given day.

Solving this in Power Query enabled very simple filtering and measures to take place at report level.

[Anonymous]

Hi @LeeGrimshaw1 

I'm glad to know that you solved this problem and thank you for sharing your solution. This will benefit others who are experiencing similar problems.

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

[DataNinja777]

Hi @LeeGrimshaw1 ,

The DAX measure below will produce the output you requested; however, the result differs from what is shown in your second picture

Number of days open that could have been worked = 
    sumx('Calendar',
        if(max('Table'[Ticket Start Date]) <= 'Calendar'[Date]&& 
        max('Table'[Ticket End Date]) >= 'Calendar'[Date]&& 'Calendar'[Available to be worked?] = "Available",1,0)
    )

Please verify if the result matches your intentions

I have attached an example pbix file. 

[bhanu_gautam]

@LeeGrimshaw1 , Create a measure for 

OpenDays =

VAR StartDate = MIN('DateTable'[Date])

VAR EndDate = MAX('DateTable'[Date])

RETURN

SUMX (

    FILTER (

        Tickets,

        Tickets[OpenDate] <= EndDate &&

        (Tickets[CloseDate] >= StartDate || ISBLANK(Tickets[CloseDate]))

    ),

    DATEDIFF (

        MAX ( StartDate, Tickets[OpenDate] ),

        MIN ( EndDate, COALESCE ( Tickets[CloseDate], EndDate ) ),

        DAY

    ) + 1

)

Create a measure for 

NonWorkingDaysCount =

CALCULATE (

    COUNTROWS ( NonWorkingDays ),

    NonWorkingDays[Date] >= MIN ( 'DateTable'[Date] ) &&

    NonWorkingDays[Date] <= MAX ( 'DateTable'[Date] )

)

AdjustedOpenDays =

[OpenDays] - [NonWorkingDaysCount]