topic Re: Help on How to Create a Measure Based on Start & End Date in DAX Commands and Tips

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

andybamber — Mon, 11 Apr 2022 10:38:52 GMT

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

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

amitchandak — Mon, 11 Apr 2022 10:45:08 GMT

andybamber , refer if one of these 2 can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

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

andybamber — Mon, 11 Apr 2022 12:53:33 GMT

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

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

tamerj1 — Tue, 12 Apr 2022 03:26:37 GMT

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

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

andybamber — Mon, 25 Apr 2022 14:03:59 GMT

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

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

tamerj1 — Mon, 25 Apr 2022 14:32:28 GMT

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

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

andybamber — Tue, 26 Apr 2022 06:41:15 GMT

thanks tamerj1 , its appreciated!

Cheers

Andy

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

tamerj1 — Wed, 27 Apr 2022 09:32:07 GMT

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:

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.
The [Active Users] Measure has been updated accordingly.
The [Leaving Users] Measure has been added.
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!