Relative Date Does not Work with "Show Items Without Data"

[TimmK]

I want to see all months between a specific range even if the values are blank. Thus, I selected "Show items without data".

Yet, then the relative date filter does not work anymore. I erroneously see months before and after the desired time span. In this case I only want to see months between 2019 and the current month.

How can this be solved?

[johnt75]

You could try adding a new column onto your Date table to act as a filter, e.g. if you wanted to show the last 24 months add a column as

Within 24 months = IF ( 'Date'[Date] <= TODAY() && 'Date'[Date] >= DATE( YEAR(TODAY()), MONTH(TODAY()) - 24, 1 ), 1 )

then add the new column onto your visual as a filter to only show when the value is 1

[TimmK]

Thanks, but this does not work. The blank months before and after the time frame are still shown.

[johnt75]

The only other thing I could suggest is to amend the base measure, or create another version of it if you are using it elsewhere.

The basic principle would be to check whether or not the date is within the 24 month window and only return a value if it is within the window. Wrap the return in a COALESCE statement so that it will return 0 instead of blank. You could then switch off the "show items with no data".