I'm using a slicer that uses an unrelated date table to set the maximum date of the main date table, without filtering that main table to a single month (like what would happen if the slicer were using the main date table instead).
However, since they're unrelated, the date comparison ( Datetable1 <= Selected(DateTable2) ) won't work.
This is my code for a calculated column, which will be put into a table:
------------------------------------------------------------------------------
Test 2 (filtered date column) =
VAR currMonth =
SELECTEDVALUE('Independent Date Table'[Date])
// Recalculated for every date selection.
VAR currPeriod =
FILTER(
ALLSELECTED(Sheet1[Period - month]),
Sheet1[Period - month] <= currMonth
)
// ALLSELECTED() Excludes months that are missing within the context (e.g. months measured quarterly) - causing it to be non-consecutive.
RETURN currPeriod
---------------------------------------------------------------------
The bit in red seems to not be evaluating correctly, it seems to just be returning false for everything, and therefore returning a blank table.
I have tried adding an inactive relationship between the two date columns, and then using USERELATIONSHIP() to activate it just during the calculation, like so:
--------------------------------------------------------------------
Test 2 (filtered date column) =
VAR currMonth =
SELECTEDVALUE('Independent Date Table'[Date])
// Recalculated for every date selection.
VAR currPeriod =
FILTER(
ALLSELECTED(Sheet1[Period - month]),
CALCULATE(SELECTEDVALUE(Sheet1[Period - month]) <= currMonth,
USERELATIONSHIP(Sheet1[Period - month], 'Independent Date Table'[Date]))
)
/*ALLSELECTED() Excludes months that are missing within the context (e.g. months measured quarterly) - causing it to be non-consecutive.
USERELATIONSHIP() activates the relationship with the independent date table just for this calculation, so that the date comparison can work. */
RETURN currPeriod
-------------------------------------------------------------------
However this still returns a blank column.
What am I doing wrong? Is there a better way to do this?
