Bypass date filters on visual

[Loubot3000]

CONTEXT:

I'm trying to visualize a list of months up to the current selected month (controlled by a slicer), excluding months that are not in the data set (say for example it were filtered by a country that doesn't have data for August 2022).

CODE:

Test =

VAR currMonth =

    SELECTEDVALUE(Sheet1[Period - month])

// Recalculated for every date selection.

VAR currPeriod =

    FILTER(

        ALLSELECTED(Sheet1[Period - month]),

        Sheet1[Period - month] <= currMonth

    )

// Returns a table of months up to the selected month, excluding missing months.

return currPeriod

Note: Sheet1[Period - month] is a date-type column.

ISSUES:

a) When placed in a visual that is filtered by a slicer, yes the measure does fetch the current month, but the slicer also filters ALLSELECTED() in currPeriod and filters the output to only that current month too.

b) In the FILTER() function, Sheet1[Period - month] <= currMonth causes the measure (or in this case a calculated column, so I can view it to debug) to not return any values. When I replace that with some arbitrarily chosen date, e.g. Sheet1[Period - month] <= DATE(2023,3,1) it does indeed return values. I need it to automatically calculate that date from the selected month. Maybe this line of code is expecting a different variable type? Also to be more accurate, it only returns a single value, leading to the next issue:

How do I get it to a) actually return dates correctly and b) bypass the visual filter for ALLSELECTED() in currPeriod and in the output - or is there another way of doing all this that I'm missing?

Thanks 🙂

ADDITIONAL INFO:

This is just a snippet of code from a bigger piece used to make a non-consecutive 3-month average that calculates for the selected month. I managed to implement it correctly in a graph that has month on the x-axis by using MAX() as the current month because it is recalculated for every month on the x-axis. But this is different, because the slicer filters the entire visual.

[Loubot3000 · Accepted solution]

I have made a seperate date table using the CALANDER() function, that isn't related to the rest of my dataset. I then have the slicer select dates from that independent date table, to filter the visual. The measure in the visual retrieves the selected date, and then uses it to filter the actual dates that are in the dataset - which themselves are not filtered by the slicer.

However, this filter doesn't seem to be behaving correctly.

The filter argument in:

FILTER(

        ALLSELECTED(Sheet1[Period - month]),

        Sheet1[Period - month] <= currMonth

    )

seems to be evaluating as False. However when I flip the operator to >=, it evaluates as true and gives me the column of dates, but only 2018 up to the max date in the dataset, rather than the min up to the selected value.

What on earth is going on?

To be clear, they're all definitely date variables.

It works properly in a different context where instead of SELECTEDVALUE(Independent Date Table[Date]) I have MAX(Sheet1[Period - month]) and filter for Sheet1[Period - month] <= MAX(Sheet1[Period - month]) which is recalculated for every month in a bar chart.

So what could possibly be causing this?

[Loubot3000]

I've realised that the reason it can't do the date comparison is because the tables are not related. This is such a catch-22.

There's the possibility of creating an inactive relationship, and then using the following code to activate it:
USERELATIONSHIP(Sheet1[Period - month], 'Independent Date Table'[Date])

However I can't seem to shuffle the code around in just the right way to make it work.

Anyway, since, in a way, I've solved the first part, I'll change the title of the post and mark this as solved and make a seperate post about the second part.