Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
andybamber
Helper III
Helper III

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

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

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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 )
        )
    )

 

View solution in original post

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 )
    )

 

1.png

Have a great day!

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

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 )
        )
    )

 

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

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

thanks @tamerj1 , its appreciated!

 

Cheers

Andy

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 )
    )

 

1.png

Have a great day!

andybamber
Helper III
Helper III

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
Super User
Super User

@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...

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.