Forum Discussion
Creating filter where date value can fall into different buckets
I want to create a filter for "next 7 days" and also "next 14 days", where a date might fall into both categories. Been trying to do this a while without success, i had the following code as an approach and it works for the next 7 days, but returns the wrong output for next 14 days.
I want next 14 days to show me 14 days from today, but it returns days 7-14 only.
TimePeriods = SWITCH(
TRUE(),
dim_date_bi[date] >= TODAY() && dim_date_bi[date] < TODAY() + 7, "Next 7 Days",
dim_date_bi[date] >= TODAY() && dim_date_bi[date] < TODAY() + 14, "Next 14 Days",
BLANK()
)Create a new table like
Time Period Slicer = SELECTCOLUMNS ( UNION ( GENERATE ( { "Next 7 Days" }, DATESINPERIOD ( dim_date_bi[date], TODAY (), 7, DAY ) ), GENERATE ( { "Next 14 Days" }, DATESINPERIOD ( dim_date_bi[date], TODAY (), 14, DAY ) ) ), "Period", [Value1], "Date", [Value2] )Create a many-to-many relationship from the new table to your date table, single direction so that the new table filters date, not the other way around.
Use the new table in your slicer.
3 Replies
Create a new table like
Time Period Slicer = SELECTCOLUMNS ( UNION ( GENERATE ( { "Next 7 Days" }, DATESINPERIOD ( dim_date_bi[date], TODAY (), 7, DAY ) ), GENERATE ( { "Next 14 Days" }, DATESINPERIOD ( dim_date_bi[date], TODAY (), 14, DAY ) ) ), "Period", [Value1], "Date", [Value2] )Create a many-to-many relationship from the new table to your date table, single direction so that the new table filters date, not the other way around.
Use the new table in your slicer.
