Forum Discussion

LeeGrimshaw1's avatar
LeeGrimshaw1
Frequent Visitor
2 years ago
Solved

How many days open in a given time frame

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.

 

 

 

 

 

 

 

Need Help
  • LeeGrimshaw1's avatar
    LeeGrimshaw1
    2 years ago

    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.

     

     

     

     

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    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]
     
     
  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    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. 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      LeeGrimshaw1
      Frequent Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.