Forum Discussion

SteveG_91's avatar
SteveG_91
Helper I
4 years ago

Multiple Slicers on One Line Chart

Greetings.  

I have a table (named ITC_DAILY_STATS) that contains applications received throughout the month.  There is one row per application.  I have a line chart that compares, year over year, cumulative applications counts by business day (note that our months do not follow calendar days.  The beginning of the month often begins on the 2nd calendar date and runs through the 1st calendar date of the next month).  This line chart works fine and accurately compares the month selected by a “Month” slicer.  It’s limitation is that it always compares to the same month from the previous year (ex. June 2022 vs June 2021).

 

This is the measure I use to get the cumulative counts:

MTD App Count 2 = calculate(sum('ITC_DAILY_STATS'[Run Sum]),filter(all('ITC_DAILY_STATS'[Business Day]),'ITC_DAILY_STATS'[Business Day]<=max('ITC_DAILY_STATS'[Business Day])))

 

 

What I would like to do is have another line chart, with the same cumulative daily count, but allow the user to select the two months to compare, controlled by two slicers.  Do I need two new measures, one for each month that the user selects on the slicers?  How do I include them in the same line chart visual?  The slicers will select the month/year from 'DimDate'[Month Year Name]. 

 

Thanks in advance. 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    SteveG_91 , Cummulative should work on your calendar

    Same way 1 year behind the measure should also work with date table

     

    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

     

    For two slicer selections refer

    How to use two Date/Period slicers

    https://youtu.be/WSeZr_-MiTg

     

     

    Calendar that starts with any date of the month — Just two variables apart
    https://medium.com/chandakamit/cheat-sheet-calendar-that-starts-with-any-date-of-the-month-just-two-variables-apart-9fb9a0f65b92

    • SteveG_91's avatar
      SteveG_91
      Helper I
      4 years ago

      amitchandak I created the Range 2 measure as you described in your video however my table does not display any data.  This is my measure

       

      Range 2 =
      var _max = maxx(ALLSELECTED(DimDate),DimDate[Month Year Name])
      var _min = minx(ALLSELECTED(DimDate),DimDate[Month Year Name])
      Return
      //calculate(sum('ITC_DAILY_STATS'[Run Sum]),filter(all('ITC_DAILY_STATS'[Business Day]),'ITC_DAILY_STATS'[Business Day]<= _max && 'ITC_DAILY_STATS'[Business Day] >= _min),REMOVEFILTERS(DimDate[Month Year Name]))
      calculate(sum('ITC_DAILY_STATS'[Run Sum]),filter(all(DimDate),DimDate[Month Year Name]<= _max && DimDate[Month Year Name] >= _min))
       

      Clearly I'm missing something