Forum Discussion

KP-PowerBI's avatar
KP-PowerBI
Frequent Visitor
7 years ago
Solved

Month Slicer Filtering with two tables using different comparisons

Hi All

 

I'm trying to figure out how I would use one slicer which has a single select list of month-year to be able to filter on two tables using different comparisons.

 

So for example on Table 1 the data is month-year only, so I want the comparison to be equal to the slicer. But on Table 2 I want it be equal or earlier than the last date of the selected month-year on the slicer.

 

Hope that makes sense.

 

Is that possible?

 

Thanks in advance

Need Help

2 Replies

    • KP-PowerBI's avatar
      KP-PowerBI
      Frequent Visitor
      7 years ago

      Thanks for this it helped a lot and got me in the right direction.

       

      For anyone reading this thread what I did was created a MonthTable, with MonthName and Year and a calculated column to work out the MonthEndDate from that. I then created two measures in my MonthTable with a filter check on the field I am using as a slicer. One for 'MonthNameYear' and one for 'MonthEndDate'. The formula for MonthEndDate is as follows (similar for MonthNameYear):

       

      SelectedMonthEndDate = IF(ISFILTERED(MonthTable[MonthName-Year]),FIRSTNONBLANK(MonthTable[MonthEndDate],1),BLANK())

       

       

      Then for the table with Monthly Data I created a measure with a filter based on the selection of the MonthTable Slicer.

       

      SumTarget= CALCULATE(SUM('MonthlyData'[Target]), FILTER('MonthlyData', MonthlyData'[MonthName Year] = MonthTable[SelectedMonthNameYear]))

       

       

      Then for the table with actual dated data I created a similar measure but using a date comparison

       

      ScoreTarget = CALCULATE(DIVIDE('FullData'[TotalOnTarget] + 0,[Total] + 0),FILTER('FullData', 'FullData'[targetdate] <= MonthTable[SelectedMonthEndDate]))

       

       

      Hope that helps.