Dynamic range CALENDAR with RLS

[Plfrcs]

Hello,

I want to set up a calendar that scales according to the minimum and maximum date of a column for each of the RLS roles.

Example:

- Global role :
Min column date : 01/01/2021, Max column date: 12/31/2021
Calendar start date : 01/01/2021, Calendar end date: 31/12/2021
Year month segment : 01/21 to 12/2021

- Role 1 :

Minimum date column : 05/06/2021, Maximum date column: 25/07/2021
Calendar start date : 05/06/2021 , Calendar end date: 25/07/2021
Year month Slicer : 06/21 to 07/2021

- Role 2:
Minimum date column : 08/09/2021, Maximum date column: 10/11/2021
Calendar start date : 08/09/2021 , Calendar end date: 10/11/2021
Year month Slicer : 09/21 to 11/2021

By setting the calendar start date to MIN(table[column date]) or FIRSTDATE(table[column date]) , the MIN/FIRSTDATE functions bypasses the RLS by always bringing up the global minimum date

Thanks

[Aburar_123 · Accepted solution]

Hi @Plfrcs ,

Please find the below solution,

Date Dimension table,

Create the below measure,

Date_Range_Measure =

IF(MAX(Date_Dim[Date])>=MIN('User Table'[Start_Date]) && format(MAX(Date_Dim[Date]),"YYYYMM")<=FORMAT(MAX('User Table'[end_Date]),"YYYYMM"),1,0)

Create a slicer using Month-Year column in the Date_Dim table and place the above created measure into the Visual level filter and set its value as 1,

By default it would be like below,

While you do the RLS based on the user, Dates in the slicer will be based upon the selected user as below,

Please like and mark my post as a solution if it answers your question. Thanks.

[lbendlin]

You have to distinguish between row filtering (what RLS does) and DAX function basics - if you use a function like CALENDARAUTO it will always create full calendar year ranges even if you limit the fact dates.

Use your own Calendar table, and refrain from using certain DAX time intelligence functions.