Forum Discussion
How many days open in a given time frame
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.
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.
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.
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.