Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Cheers
Andy
Solved! Go to 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 )
)
)
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 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!
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
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 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!
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
@andybamber , refer if one of these 2 can help
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
19 | |
14 | |
11 |
User | Count |
---|---|
43 | |
35 | |
25 | |
22 | |
22 |