Calculate a subset of a measure based on another table

[Anonymous]

Hi,

I am currently having a date table I use as slicer and I have:

1st Table

Table with all the visitors (one record for every day they visit it, therefore I have this DAX measure to calculate the Active Users:

Active Users (NEW) = CALCULATE(

DISTINCTCOUNT(Analytics_Visitors[Email]),

ALLSELECTED('calendar'[Month])

)

Visit Date	Email	Account ID
15/01/2022	personab@gmail.com	AB12345
16/01/2022	personab@gmail.com	AB12345
19/01/2022	xyz@gmail.com	XZ9876
25/02/2022	xyz@gmail.com	XZ9876

In this example the active user formula returns 2 for Jan and 1 for February (The active users are defined with at least 1 visit per month.

2nd Table

Table with the users being the paid license holders, each email has a start and end date for when they are a named paid user. And calculating the total like this:

Total Named License Holders =

VAR CurrentDate = MAX('calendar'[Date])

VAR PaidUsers =

CALCULATE(

COUNTROWS('Paid Users - Master Data'),

ALL('calendar'),

'calendar'[Date]<=CurrentDate,

ISBLANK('Paid Users - Master Data'[LicenseExpirationDate])

|| 'Paid Users - Master Data'[LicenseExpirationDate]>=CurrentDate

)

VAR Result =

IF(

YEAR(CurrentDate)<=YEAR(TODAY()),

PaidUsers

)

RETURN

Result

Data example:

Account ID	Email	Paid License Start Date	Paid License End Date
AB12345	personab@gmail.com	1/1/2022	31/3/2022
XZ9876	xyz@gmail.com	12/1/2022	12/4/2022
QW567	nzc@test.com	1/1/2022	31/12/2022

The 2 tables are joined using a mapping table as a bridge with Account ID.

What I am trying to get is a measure that tells me the amount of total Active Named Users.

Let's say I have 10 Named Licenses in February (10 emails in the second table), and only 5 of them are active for that month (5 of these emails in the first table of active users) , I would like the measure to return 5.

Can anybody help with that?

[v-yalanwu-msft]

Hi, @Anonymous ;

Sorry, In your table I can't find  the [LicenseExpirationDate] column however your measure had; In my understand, you could create this measure.

Active Users (NEW) = CALCULATE(
DISTINCTCOUNT(Analytics_Visitors[Email]),
FILTER( 'Analytics_Visitors',EOMONTH([Visit Date],0)=EOMONTH(MAX('calendar'[Date]),0)))

Total Named License Holders = 
CALCULATE(DISTINCTCOUNT('Paid Users - Master Data'[Account ID]),
FILTER('Paid Users - Master Data',[Paid License Start Date]<=MAX('calendar'[Date])&&[Paid License End Date]>=MAX('calendar'[Date])))

diff = [Total Named License Holders]-[Active Users (NEW)]

The final show:

If not right ,can you share the example and the result you want to output?
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for your help, I don't need the difference but the number shared between the 2 tables for the same period (Paid Users who were active). 

I tried to solve it in this way, what do you think about this?

Active PAID Users =
VAR CurrentDate = MAX('calendar'[Date])
VAR PaidUsers =
CALCULATETABLE(
VALUES('Paid Users - Master Data'[email]),
ALL('calendar'),
'calendar'[Date]<=CurrentDate,
ISBLANK('Paid Users - Master Data'[LicenseExpirationDate])
|| 'Paid Users - Master Data'[LicenseExpirationDate]>=CurrentDate
)
return CALCULATE( [Active Users], TREATAS( PaidUsers, Analytics_Visitors[Email]) )