RLS by Working Days

Power BI Data Model
1

Hi all,

I am still finding my feet on how RLS works and been watching a few video’s, but I would appreciate any help.

For simplicity, let us assume that my model consists of a sales and date table. I am trying to implement an RLS with the below criteria based on the following scenario:

“We are in March, and today is WD5, and I am still working and updating data for the Feb report. Reporting Month slicer is selected on Mar”

Standard users (Viewer):

  • Can only see data in the report if we are on or after WD8.
  • If we are not on WD8, then they can only see data up to last month (Jan).
  • They can’t see any future data in the report (i.e. Mar & Apr).

Manager’s: No restrictions.

**Update: I am attaching a sample file that includes WD numbers. RLS for WD.pbix (465.6 KB)

Many thanks
Hesham

1 Like
2

Found the solution!

RLS for Standard user role:

VAR _Today = TODAY()
VAR _WD = LOOKUPVALUE ( 'Date'[WD Number], 'Date'[Date], _Today )
VAR _EOLM = DATE ( YEAR ( _Today ), MONTH ( _Today ), 1 ) -1
VAR _EOM = EOMONTH( _Today, 0 )
RETURN 
IF ( _WD < 5, 
'Date'[Date] <= _EOLM,
'Date'[Date] <= _EOM
)
1 Like