Forum Discussion

HoustonLamb's avatar
HoustonLamb
Frequent Visitor
2 years ago

single slicer for multiple dates in one table

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

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

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.