Help on How to Create a Measure Based on Start & End Date

[andybamber]

Hello!

Hoping someone could help me with some advice on how to build a measure

I have a table of users, with start and/or end timestamps. The table is an access history for the users.

Some users appear multiple times where they have access to multiple org units e.g. Name6, and some others are there multiple times where their access has changed, and the previous access has ended e.g. Name9

What I am trying to show on a year/month basis is the distinct count of users who had an ACTIVE access profile in that period. So for instance if I looked at November 2021 i would see a distinct count of 2... It needs to simultaneously look at the starttimestamp and endtimestamp to determine whether the user was active in that period. Again if we look at Name2 i would expect them to be counted in March 2022 and then April 2022 even though their end timestamp was half way through April

E.g file below 

PBIX FILE 

Cheers

Andy

[tamerj1 · Accepted solution]

Hi @andybamber 
Here is the sample file with the solution https://www.dropbox.com/t/J56gVd2IbetcntOR
For active users I assume a rolling number that, by time, increases when new users join and decreases when users leave. Therefore, when you say:
"So for instance if I looked at November 2021 i would see a distinct count of 2"
I would consider that you mean 2 members will be added to the moving (rolling) total. I hope this is what you mean. 
One more thing need mention; this won't work properly if you have more than two start dates. The results won't be so accurate. If this is the case please provide data that reflects the actual scenario.

New Users = 
IF ( 
    HASONEVALUE ( Date_Table[MonthInCalendar] ),
    DISTINCTCOUNT ( 'Table'[User] )
)

Active Users = 
VAR FirstDateInFilter =
    MIN ( Date_Table[Date] )
VAR LastDateInFilter =
    MAX  ( Date_Table[Date] )
RETURN
    IF (
        HASONEVALUE ( Date_Table[MonthInCalendar] ),
        CALCULATE (
            SUMX ( 
                VALUES ( 'Table'[User] ),
                VAR StartDate = 
                    CALCULATE ( MIN ( 'Table'[StartTimestamp] ) )
                VAR EndDate =
                    CALCULATE ( MAX ('Table'[EndTimestamp] ) )
                RETURN
                    IF (
                        StartDate <= LastDateInFilter
                            && OR ( ISBLANK ( EndDate ), EndDate >= FirstDateInFilter ),
                        1,
                        0
                    )
            ),
            REMOVEFILTERS ( Date_Table )
        )
    )

[tamerj1 · Accepted solution]

Hi @andybamber 
Here is the sample file with the solution https://www.dropbox.com/t/J56gVd2IbetcntOR
For active users I assume a rolling number that, by time, increases when new users join and decreases when users leave. Therefore, when you say:
"So for instance if I looked at November 2021 i would see a distinct count of 2"
I would consider that you mean 2 members will be added to the moving (rolling) total. I hope this is what you mean. 
One more thing need mention; this won't work properly if you have more than two start dates. The results won't be so accurate. If this is the case please provide data that reflects the actual scenario.

New Users = 
IF ( 
    HASONEVALUE ( Date_Table[MonthInCalendar] ),
    DISTINCTCOUNT ( 'Table'[User] )
)

Active Users = 
VAR FirstDateInFilter =
    MIN ( Date_Table[Date] )
VAR LastDateInFilter =
    MAX  ( Date_Table[Date] )
RETURN
    IF (
        HASONEVALUE ( Date_Table[MonthInCalendar] ),
        CALCULATE (
            SUMX ( 
                VALUES ( 'Table'[User] ),
                VAR StartDate = 
                    CALCULATE ( MIN ( 'Table'[StartTimestamp] ) )
                VAR EndDate =
                    CALCULATE ( MAX ('Table'[EndTimestamp] ) )
                RETURN
                    IF (
                        StartDate <= LastDateInFilter
                            && OR ( ISBLANK ( EndDate ), EndDate >= FirstDateInFilter ),
                        1,
                        0
                    )
            ),
            REMOVEFILTERS ( Date_Table )
        )
    )

[andybamber]

Hi @tamerj1 

So regarding this problem I had, can it be developed so that people with multiple start dates are counted? I notice in my real set of data that as we had more people  the actual results started to differ from reality by quite a margin! In the original data set there are some examples of this, for instance Name11 has 35 rows all with a start date of 13/4/2022... this is very possible as each row represents access to a different business unit, Name11 should, however, only be counted once, whereas at the moment they are not counted at all

Cheers

Andy

[tamerj1]

I'll look into it and get baxk to you. Please remide me in a private message if I didn't

[andybamber]

thanks @tamerj1 , its appreciated!

Cheers

Andy

[tamerj1 · Accepted solution]

Hi @andybamber 

Here is the updated file https://www.dropbox.com/t/1g0PtekIviN7lpGU
Not sure if I correctly understand the what is required but I did the following:

1. I modified the code [New Users] in oder to include duplicates in the count. So instead of counting user ID's we now count the number of rows.
2. The [Active Users] Measure has been updated accordingly.
3. The [Leaving Users] Measure has been added.
4. Fixed the Total to show correct values.

I hope this satisfies your requirements.

New Users = 
SUMX ( 
    VALUES ( Date_Table[MonthInCalendar] ),
    CALCULATE ( COUNTROWS ( 'Table' ) )
)

Leaving Users = 
VAR CurrentDateRange = VALUES ( Date_Table[Date] )
RETURN
    CALCULATE ( 
        SUMX ( 
            'Table',
            VAR EndDate = 'Table'[EndTimestamp]
            RETURN
                IF (
                    DATE ( YEAR ( EndDate ), MONTH ( EndDate ), DAY ( EndDate ) ) IN  CurrentDateRange
                        && NOT ISBLANK ( 'Table'[EndTimestamp] ),
                    1
                )
        ),
        REMOVEFILTERS ( Date_Table )
    )

Active Users = 
VAR LastDateInFilter =
    MAX  ( Date_Table[Date] )
VAR FirstDateInFilter =
    DATE ( YEAR ( LastDateInFilter ), MONTH ( LastDateInFilter ) - 1, 1 )
RETURN
    CALCULATE (
        SUMX ( 
            'Table',
            VAR StartDate = 
                'Table'[StartTimestamp]
            VAR EndDate =
                'Table'[EndTimestamp]
            RETURN
                IF (
                    StartDate <= LastDateInFilter
                        && OR ( ISBLANK ( EndDate ), EndDate >= FirstDateInFilter ),
                    1
                )
        ),
        REMOVEFILTERS ( Date_Table )
    )

Have a great day!

[andybamber]

Hi @amitchandak 

So, this measure almost does what I need, except it does not provide a distinct count of user...

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

Do you know how i could incorporate this?

Thanks

[amitchandak]

@andybamber , refer if one of these 2 can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...