Forum Discussion
Use selected date (slicer) to filter rows based on 2 date columns
Hi everyone,
I have a big table with several columns, being two of them start date and end date, sales.
What I want to do now is give a slicer to the user where he selects a date ( it can either full date dd/mm/yyyy or just mm/yyyy ), with this date I want to :
- remove rows that have end date before that date
- remove rows that have start date after that date
This is so I can then calculate some metrics based on the rows left , one of them sales.
Really appreciate the help !
Hi PedroFerreira ,
Try to create another Calendar table with Date column as a slicer and don't create relationships among your tables. After that, create measures with the date filters like so:
Measure = VAR SlicerDate_ = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[Sales] ), 'Table'[StartDate] <= SlicerDate_ && 'Table'[EndDate] >= SlicerDate_ )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- Icey
Community Support
Hi PedroFerreira ,
Try to create another Calendar table with Date column as a slicer and don't create relationships among your tables. After that, create measures with the date filters like so:
Measure = VAR SlicerDate_ = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[Sales] ), 'Table'[StartDate] <= SlicerDate_ && 'Table'[EndDate] >= SlicerDate_ )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you ! It worked perfectly
