Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |