topic DAX measure to calculate users who are active at the selected timeframe in DAX Commands and Tips

DAX measure to calculate users who are active at the selected timeframe

Praj — Tue, 27 Feb 2024 10:54:28 GMT

Hey Everyone,

I am trying to calculate the % of users who had an event and had an active membership during the selected timeframe. For example, I have below two tables.

1) Membership Info - Table about when a member joined the membership

2) Event Info - Table when a member had an event

I want to calulate the % of member who had an event and were active at the selected timeframe using the below slicer. Say, If my user selects April and May in the slicer, the output should be 100% because at the end of May, 3 users had event and their membership start date was before May. Similarly, If my user select July, the output should be 25% as only 1 user had en event in July and by the end of July we had 4 users whose membership start date was <= 31st of July

I am able to get the distinct user who had an event (i,e I am able to get the right number for numerator) however, facing error with denominator. I did write the below code but with incorrect result

Measure = var selected_date = SELECTEDVALUE(Sheet2[Event Date]) var memberswithmemship = CALCULATETABLE( VALUES(Sheet1[User ID]), Sheet1[Membership Date] <= selected_date ) return COUNTROWS(memberswithmemship)

If anyone had similar usecase or anyone know a possible solution/approach I would be really grateful if you share the same.

Thanks in advance

Praj

Re: DAX measure to calculate users who are active at the selected timeframe

lbendlin — Tue, 27 Feb 2024 21:11:58 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: DAX measure to calculate users who are active at the selected timeframe

Praj — Wed, 28 Feb 2024 10:48:30 GMT

Hi lbendlin ,

Thanks for your response. Below is the sample data as provided in the screenshot. I am trying to calulate the % of members who had an event and were part of membership at the selected timeframe.Say, If my user selects April and May in the slicer, the output should be 100% because at the end of May, 3 users had event and their membership start date was before May. Similarly, If my user select July, the output should be 25% as only 1 user had en event in July and by the end of July we had 4 users whose membership start date was <= 31st of July

Membership Info Table

User ID	Membership Date
1	01-Jan-22
2	01-Jan-22
3	01-Apr-22
4	01-Jun-22
7	01-Aug-22
5	01-Sep-22
6	01-Sep-22
8	01-Oct-22
9	01-Nov-22
10	01-Dec-22

Event Info

User ID	Event Date
1	01-Apr-22
2	01-Apr-22
3	05-Apr-22
1	01-May-22
4	01-Jul-22
5	01-Oct-22
5	05-Oct-22
8	05-Oct-22
9	09-Dec-22
10	09-Dec-22

Re: DAX measure to calculate users who are active at the selected timeframe

lbendlin — Wed, 28 Feb 2024 23:48:46 GMT

your sample data is not covering your scenarios well.