I have 3 tables one has Pre Days means 1,2,3,4 and 2nd has post dates. I have a calender table which has some hard coded dates but I want to change start and end dates by appending the Predates in start and EndDates in end.
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:
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.