Slicer visual default date logic not working

[YashikaAgrawal]

Hello, need help in slicer visual.

Created a measure as below

Last 13 months filter =
var maxondate =
calculate(max(table_name(file_load_date),all(table_name)

var currentmonthdate = table_name(File_load_date)
return
if currentmonthdate >= Edate(maxondate , -12),
1,
0
)

Above logic works for table visual, i have a slicer, for dates, need to use the above logic in that, i used advance filtering and gave 1.

This is not working.

Any idea how to use last 13 months default logic in dates slicer.

Thanks,

[danextian]

Hi @YashikaAgrawal 

Your measure have several syntax error and should be like this:

Last 13 months filter =
VAR maxondate =
    CALCULATE (
        MAX ( table_name[file_load_date] ),
        ALL ( table_name )
    )
VAR currentmonthdate =
    MAX ( table_name[file_load_date] ) --  a measure requires a scalar value and a column cannot be plainly referenced  without being aggregated
RETURN
    IF (
        currentmonthdate >= EDATE ( maxondate, -12 ),
        1,
        0
    )

 This should work for dropdown and vertical slicers but not with range slicers which, in most cases, require the filter to be coming from a physical column and not a measure. You wil need to use a calculated column as what @SamInogic has suggested.

[SamInogic · Accepted solution]

Hi,

In Microsoft Power BI, the reason your approach doesn’t work is that slicers cannot be filtered by measures. Measures are calculated after filter context is applied, while slicers require columns. That’s why your measure works in a table visual but not in a slicer.

To implement a dynamic “Last 13 Months” slicer, you need to use a calculated column in your date table instead of a measure.

Recommended Solution (Calculated Column)

Create a column in your Date table (or the table used in the slicer):

Last13MonthsFlag =
VAR MaxDate =
    CALCULATE(
        MAX('table_name'[File_load_date]),
        ALL('table_name')
    )
RETURN
IF(
    'DateTable'[Date] >= EDATE(MaxDate,-12)
        && 'DateTable'[Date] <= MaxDate,
    1,
    0
)

Then:

1. Use DateTable[Date] in the slicer.
2. Add Last13MonthsFlag to the Visual Level Filters of the slicer.
3. Set the filter to = 1.

Now the slicer will only show the last 13 months dynamically.

Even Simpler Option (Recommended)

If you have a proper date table, Power BI already provides a built-in filter:

1. Select the slicer.
2. Change slicer type to Relative Date.
3. Set:
• is in the last
• 13
• Months

This is usually the cleanest solution.

Hope this helps.

Thanks!