current users and their history

[JTDQ]

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)

[devanshi]

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

[JTDQ]

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

[JTDQ]

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)

[v-jianboli-msft]

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.

[JTDQ]

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

[v-jianboli-msft · Accepted solution]

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.

[JTDQ]

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

[v-jianboli-msft]

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.

[JTDQ]

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.