Forum Discussion
Month Slicer Filtering with two tables using different comparisons
Try solution mentioned in this file.
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!
Try solution mentioned in this file.
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!
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.