Month Slicer Filtering with two tables using different comparisons

[KP-PowerBI]

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

[Anonymous · Accepted solution]

@KP-PowerBI 

Try solution mentioned in this file.

https://www.dropbox.com/s/4v9886q51bhenmv/Month%20Slicer%20Filtering%20with%20two%20tables%20using%2...

You can edit the formula based on your Month-Year format.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Proud to be Datanaut!

[KP-PowerBI]

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.