How to work with tables using security roles: Calculations only for fitting dates

[OliTFD]

My problem is a little difficult to explain:
I use BI to show our fellows their corresponding dates, therefore I use the roles. One of the tables filtered for the role is a date table called "Schultage" (school days", so the fellows only see the their active working period. The DAX filter used in role definition is

This works great... until it comes to calculations. The full "Schultage"-Table starts at August, 1st, 2020 (01.08.2020 in german notation). And every calculation I use allways starts with this date, not the first day visible for the fellow. Which, in this example, is August, 4th, 2021 (04.08.2021).
Any idea how to solve this? Everything I tried had no effect ;-(
The numbers in red are what I want to get. It is the running total of "is schoolday", but only in the context of the visible part of "Schultage", not the whole table.

[OliTFD]

Dear Rean,

thanks a lot! I needed some additional measures to calculate the first working day from the Princiapaluser, and ad this measure to your idea. Now I get correct results.

First, I calculate the users mailadress:

Aktiver Fellow = Calculate(MIn(Fellows[Fellow-Mailadresse]))

Then, I get his first working day:

Aktiver Fellow erster Arbeitstag = LOOKUPVALUE(Fellows[Erster Arbeitstag],Fellows[Fellow-Mailadresse],'AA Measures'[Aktiver Fellow])

This could be done in one calculation, sure 😉

Then I add this value to your calculation as minimum date:

Schooldays Running Total =

VAR _mindate =

CALCULATE ( 'AA Measures'[Aktiver Fellow erster Arbeitstag], ALLSELECTED ( 'Schultage' ))

VAR _schooldays =

CALCULATE (

COUNT ( 'Schultage'[Is schoolday] ),

FILTER (

ALLSELECTED ( 'Schultage' ),

'Schultage'[Is schoolday] = 1

&& 'Schultage'[Date] <= SELECTEDVALUE ( 'Schultage'[Date] )

&& 'Schultage'[Date] >= _mindate

)

)

RETURN

If( _schooldays-40 < 0,0,_schooldays-40)

In the last line, there is some minor addition: I only count after working day 40.
Thanks a lot for your help!

[Anonymous · Accepted solution]

Hi @OliTFD ,

You can create a measure as below, please find the details in the attachment.

Schooldays Running Total = 
VAR _mindate =
    CALCULATE ( MIN ( 'Schultage'[Date] ), ALLSELECTED ( 'Schultage' ) )
VAR _schooldays =
    CALCULATE (
        COUNT ( 'Schultage'[Is schoolday] ),
        FILTER (
            ALLSELECTED ( 'Schultage' ),
            'Schultage'[Is schoolday] = 1
                && 'Schultage'[Date] <= SELECTEDVALUE ( 'Schultage'[Date] )
                && 'Schultage'[Date] >= _mindate
        )
    )
RETURN
    _schooldays

Best Regards