Dynamic Data Slicer filtering date column

[EugenioProlog]

Hello!

I have a simple data slicer filtering a date column. Is there a way to make my Max limit date to be The last day of the NEXT month? (it has to change dynamically every month)

[v-ssriganesh]

Hi @EugenioProlog,
Thank you for reaching out to the Microsoft Fabric Forum Community, and thanks to @lbendlin & @danextian for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[danextian · Accepted solution]

Hi @EugenioProlog 

You can either filter the date table in the query editor to load dates up to the last day of the next month assuming you won't have a need for them or create a column that checks whether a date is <=last date of the next month and use that as a filter. Here's a sample DAX calc column

Check = 
VAR _timezone = 8 -- Define the user's timezone offset (e.g., UTC+8)
VAR _today =
    INT ( UTCNOW () + DIVIDE ( _timezone, 24 ) )
RETURN
    CalendarTable[Date] <= EOMONTH ( _today, 1 )

[lbendlin]

You can add that filter to the Power Query transforms assuming your Dates table is in import mode and is refreshed at least monthly.