topic Re: current users and their history in DAX Commands and Tips

current users and their history

JTDQ — Wed, 21 Jun 2023 06:12:43 GMT

i need to calculate the usage of current users year to date. source table includes all usage historically so it would include users that are no longer active.

I've written measure below, but this is counting users that are no longer active.

logic should be

active users = Company usage table where as_of_date = max(as_of_date)

then layer in their usage below to this active users list

output would be just a single aggregate number which counts the users where there are usage for the year

_usagecount_ytd_email =

VAR max_date = max(Company_Usage[As_of_Date])

VAR base_date = date(year(max_date),1,1)

RETURN

CALCULATE(

DISTINCTCOUNT(Company_Usage[id]),

Company_Usage[As_of_Date]>= base_date, Company_Usage[As_of_Date] <= max_date, Company_Usage[sent_email_count] >0)

Re: current users and their history

v-jianboli-msft — Thu, 22 Jun 2023 07:00:44 GMT

Hi JTDQ ,

It sounds like you want to calculate the usage of current users year to date, but only include users who are currently active. You have written a measure, but it is counting users who are no longer active. Am I understanding this correctly?

What does your data look like? Please provide me with more details about your table or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: current users and their history

JTDQ — Thu, 22 Jun 2023 08:46:01 GMT

yes, that's correct.

here's a sample raw data.

User	Usage	Date
A	1	17-Nov-22
B	1	17-Nov-22
C	2	17-Nov-22
A	5	3-Mar-23
B	4	3-Mar-23
C	7	3-Mar-23
D	8	3-Mar-23
E	0	3-Mar-23
A	2	10-May-23
B	4	10-May-23
D	2	10-May-23
E	0	10-May-23
A	2	22-Jun-23
B	1	22-Jun-23
E	0	22-Jun-23

expected output is a card that will tell 1) how many users there are with YTD usage and 2) what's the sum of that YTD usage. another thing to consider is that we're using a date slicer so the user can chose any date and these two measures will calculate.

i.e. as of May 10, there 3 users with YTD usage totalling 25. as of Jun 22, there are 2 users with YTD usage totalling 18.

Re: current users and their history

devanshi — Thu, 22 Jun 2023 09:04:15 GMT

MaxDate = Company_Usage[As_of_Date]

YTDUsage = CALCULATE( SUM('YourTable'[Usage]), Company_Usage[As_of_Date] <= [MaxDate], Company_Usage[id] IN VALUES(Company_Usage[id] )

Re: current users and their history

JTDQ — Thu, 22 Jun 2023 09:44:55 GMT

I wrote a measure based on your structure but its still counting no longer active users (users not in max date).

_usagecount_ytd_email =

VAR max_date = max(Company_Usage[As_of_Date])

VAR base_date = date(year(max_date),1,1)

RETURN

CALCULATE(

DISTINCTCOUNT(Company_Usage[id]),

Company_Usage[As_of_Date]>= base_date, Company_Usage[As_of_Date] <= max_date, Company_Usage[sent_email_count] >0,

NBV_Company_Usage[id] IN VALUES(NBV_Company_Usage[id]))

Re: current users and their history

JTDQ — Thu, 22 Jun 2023 10:07:19 GMT

closest i got

_usagecount_ytd_email =
VAR max_date = max(Company_Usage[As_of_Date])
VAR base_date = date(year(max_date),1,1)
VAR base_list = CALCULATETABLE(
VALUES(Company_Usage[id]),
Company_Usage[As_of_Date]=max_date)
RETURN
CALCULATE(
DISTINCTCOUNT(Company_Usage[uuid]),
Company_Usage[As_of_Date]>= base_date, Company_Usage[As_of_Date] <= max_date, Company_Usage[sent_email_count] >0,
Company_Usage[id] IN base_list)

Re: current users and their history

v-jianboli-msft — Fri, 23 Jun 2023 06:39:02 GMT

Hi JTDQ ,

Please try:

Count Of User = CALCULATE(COUNT(Company_Usage[User]),FILTER('Company_Usage',[Usage]>0)) Sum Of Usage = var _a = SELECTCOLUMNS('Company_Usage',"User",[User]) var _b = DATE(YEAR(SELECTEDVALUE(Company_Usage[Date])),1,1) return CALCULATE(SUM(Company_Usage[Usage]),FILTER(ALL(Company_Usage),[User] in _a&&[Date]>=_b&&[Date]<=SELECTEDVALUE(Company_Usage[Date])))

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: current users and their history

JTDQ — Fri, 23 Jun 2023 08:12:51 GMT

for the count of user, the might not work if user E had usage historically and not in selected month. i.e. if it had usage in March but May or June was selected.

i tried repurposing your sum of usage to do a count instead, but it's not giving the correct value.

_usagecount_ytd_email_v2 =
VAR _a = SELECTCOLUMNS(Company_Usage,"id",Company_Usage[id])
VAR _b = DATE(YEAR(SELECTEDVALUE(Company_Usage[As_of_Date])),1,1)
RETURN CALCULATE(DISTINCTCOUNT(Company_Usage[id]),
FILTER(ALL(Company_Usage),Company_Usage[id] IN _a &&
Company_Usage[As_of_Date] >= _b &&
Company_Usage[As_of_Date] <= SELECTEDVALUE(Company_Usage[As_of_Date])))

Re: current users and their history

v-jianboli-msft — Fri, 23 Jun 2023 08:33:24 GMT

Hi JTDQ ,

Please try:

Count Of User = VAR _a = DATE ( YEAR ( SELECTEDVALUE ( Company_Usage[Date] ) ), 1, 1 ) RETURN CALCULATE ( DISTINCTCOUNT ( Company_Usage[User] ), FILTER ( 'Company_Usage', CALCULATE ( SUM ( Company_Usage[Usage] ), FILTER ( ALL ( Company_Usage ), [User] = EARLIER ( Company_Usage[User] ) && [Date] >= _a && [Date] <= EARLIER ( Company_Usage[Date] ) ) ) > 0 ) )

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: current users and their history

JTDQ — Mon, 26 Jun 2023 13:23:55 GMT

this gives the same output as my "closest i got" post above. consequently, this is probably also correct. Thank you!!