single slicer for multiple dates in one table

[HoustonLamb]

I have been stalled by an issue involving multiple dates within one table. The request is to get counts of product packages where all 3 dates fall in the date range (slicer). They also want it broken down by "New" and "Renewal" packages. The dates are a Submit Date, Approval Date, and Book Date. So if all 3 of the dates fall into the date slicer range, then count the package. Any ideas?

[Anonymous]

Hi @HoustonLamb ,

Thanks for the reply from Ritaf1983 .

A calculated column can be created to validate that the date is in range.

DateRangeIsValid = 
VAR StartDate = MIN('Date'[Date])
VAR EndDate = MAX('Date'[Date])
RETURN
IF(
    'Table'[Submit Date] >= StartDate 
    && 'Table'[Submit Date] <= EndDate 
    && 'Table'[Approval Date] >= StartDate 
    && 'Table'[Approval Date] <= EndDate 
    && 'Table'[Book Date] >= StartDate 
    && 'Table'[Book Date] <= EndDate,
    1,
    0
)

Then create a measure to calculate the value 1, which is the number of rows with dates in the range:

Count = CALCULATE(COUNTROWS('Table'),'Table'[DateRangeIsValid] = 1)

You mentioned “They also want it broken down by ‘New’ and ‘Renewal’ packages”.

What is the basis of their categorization? If you can tell me how they categorize it I will be able to better solve your problem.

You are welcome to contact me anytime.

pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[HoustonLamb]

Thank Yang. That's close, but not quite what i'm trying to accomplish. i want to count them if the fall in a date range slicer. The slicer is adjustable, so say i adjust the slicer to 3/1 - 3/31, all of those should get a 0 for the count since the dates fall outside of that date range. i have added 2 additional rows to the data so you can see that if i were to select 2/1 - 4/1 that they would get a 1 for the count, but if i did 3/1 - 4/1 they should not, because the submit date falls outside of the range.

[Ritaf1983]

Hi @HoustonLamb 
It sounds like a classic scenario for "role-play dimensions".
Please refer to the linked guides :

https://www.youtube.com/watch?v=2BxaUXlx3K4&t=10s

https://www.youtube.com/watch?v=-ViFYglNkDQ

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.