Forum Discussion

Mujahid's avatar
Mujahid
Frequent Visitor
7 years ago

Dynamics Date Calendar by Visual slicers

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.

 

Table1 = CALENDAR(DATEVALUE(DATE(2013,07,24))+SELECTEDVALUE(LowerThreshold[Value]) ,DATE(2013,07,25))
But I am unable to capture selected value in Calender table. 
As I know selectedvalue works in measure but. When I recall that measure in computed column I did not give any impact. While it's adding values in measure. 
Sortly is there any way to perform this task to change start and end date dynamically by visuals.
Interesting Links
Need Help
Show and Tell

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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()
)
    • Mujahid's avatar
      Mujahid
      Frequent Visitor
      7 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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: