Forum Discussion

shubuya's avatar
shubuya
Frequent Visitor
7 years ago

Dynamically Filter a Calendar Table based on Start & End Date from Slicer

I am trying to use the 'MonthNameLong' and 'Year' from a disconnected table but I don't know the best practice and/ or simplest way. The end user will select both to act as the end date, and another slicer has YTD & MTD. This last day of that particular period (e.g. December 2010 = 12/31/2010 or 'EOMonth') is needed to dynamically filter the dates of a Calendar Table.

 

Disconnected Table:

 

Example:

User selects Month Slicer 1: 'December' and Year Slicer 2: '2010' and Period Slicer 3: 'YTD' 

 

This measure calculates the End Date ->
Date Selector = VALUES('Date Selector'[EOMonth])
 = "12/31/2010"

 

SWITCH Function picks the appropriate formula->

Measure SWITCH = IF(HASONEFILTER(period[Period]),
SWITCH(SELECTEDVALUE(period[Period]),
"MTD", 'Table1'[Measure 1 MTD],
"YTD", 'Table1'[Measure 2 YTD]
),
BLANK()
)

 

How do I write this formula to capture YTD December 2010? ->

Measure 2 YTD = TOTALYTD([Count], 'Calendar'[Date])
 
I created the disconnected table to avoid the 'Month' & 'Year' slicers from selecting a single month in the Calendar Table, but maybe my logic wasn't correct. Please help!
 
Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    shubuya - I think you should be slicing on the date table, instead of a disconnected table. The MTD or YTD will alter the filter context, so that you will end up with the appropriate dates for your calculation. 

    • shubuya's avatar
      shubuya
      Frequent Visitor
      7 years ago

      I forgot to mention a critical part of why I didn't use the Calendar Table for the slicers:

       

      I was not able to get this formula to create a line chart with the running totals (filtered YTD December 2010):

       

      Departures Running Total =
      CALCULATE(
          [Count YTD],
          FILTER(
              ALL('Calendar'[Date]),
              ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
          )
      )
       
      I need this chart to scale and show only the running total for a given interval, in this example of YTD: 1/1/2010 to 12/31/2010. The running total needs to capture the ending value as a starting point to sum the running total each period until the end date. So for example if 12/31/2009 had a total accumulated total of 59, then the line chart would need to start at 59 on 1/1/2010, incrementing until the end date of 12/31/2010.
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        shubuya - Since you are considering values from previous years, you don't want to use a YTD calculation. You're looking for something more like:

         

        Departures Running Total =
var displaydate = MAX('Calendar'[Date])
return
CALCULATE(
    <Count Measure (Not YTD)>,
    'Calendar'[Date] <= displaydate 
)