Forum Discussion
Dynamics Date Calendar by Visual slicers
Mujahid -
Calculated Tables and Calculated Columns are not affected by slicers.
You can filter the Date Table by selections in your slicers.
One way to implement this:
1. Create a Measure that will be BLANK for rows that should not appear, and "" for rows that should appear.
2. Add that measure to a table visual, along with your date.
3. If you want to hide the measure column, change the header name a single character and decrease the width of the column, so it will not appear.
The measure could be something like this:
Include Date Range =
var minDate = <Your Logic>
var maxDate = <Your Logic>
var dateToAssess = MAX('Date'[Date])
return
IF(
dateToAssess >= minDate && dateToAssess <= maxDate,
"",
BLANK()
)
I have multiple Start and End dates not a single. Initially I planned to create a table with calender and Union all dates. If If I use this measure so is it possible to append more filters with more min and max dates.
Mujahid - I'm not sure exactly what you're trying to do, but here are some tables and measures that will hopefully be a good demo of the possibilities:
Create 2 Date Tables:
Date1 = CALENDAR(DATE(2018,1,1),DATE(2020,12,31))
Date2 = CALENDAR(DATE(2018,1,1),DATE(2020,12,31))
Create a Number table for adding to the dates:
Numbers = GENERATESERIES(0,100,1)
Then a Measure to limit the dates:
Include Date Range = var minDate = MIN('Date2'[Date]) + max(Numbers[Number]) var maxDate = MAX('Date2'[Date]) + max(Numbers[Number]) var dateToAssess = MAX('Date'[Date]) return IF( dateToAssess >= minDate && dateToAssess <= maxDate, "", BLANK() )Here is the result (you need to Edit Interactions, so that one Number slicer will affect one table visual:
Can you please Share Pibx
I ran that file. It's truncating days dynamically as I select something.
My Case is bit different.
For Instance I have a table for Sales data. which has field name [Sales_Date] of multiple years. We have easter date slots (for 30 days each slot) for each year means start date of season and end date of season. So I created a table as you create with calender function and used union to uniform all years data means union(calender (start date, enddate),calender (start date, enddate),.....).
Then I created a relationship between date table and sales table. All things working fine at this stage.
Now Next thing is to add a visual if user want to see sales data preceeding N Days and Post N Days with eastern. Like it's 04/01/2019 to 04/30/2019 so user can see data for 03/26/2019 to 04/30/2019 so N Days preceeding 4. because User want to see N Days before easter with easter season.
Where I used technique mentioned about but It did not work by slicer.
If there is an other work around do let me know If i am using invalid strategy to cope up this issue.
Thank you