Forum Discussion

OliTFD's avatar
OliTFD
Regular Visitor
4 years ago
Solved

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

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 "Schult...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

OliTFD's avatar
OliTFD
Regular Visitor
4 years ago

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!