Calculating Total of Active Members

[KieftyKids]

Hi

I’m trying to work out the number of active members of a monthly giving programme on any given day.

I know how to calculate this in Excel but am struggling to get the formula right in Power BI.

The data looks like:

The Excel formula that gives the correct result based on a given date (in cell J8 on the spreadsheet) is:

=COUNTIFS('Active Members'!B:B,"<="&J8,'Active Members'!D:D,"Yes")+COUNTIFS('Active Members'!B:B,"<="&J8,'Active Members'!C:C,">"&J8)

Basically for any given day (for example 24 March 2020), to find the number of active members on that day:

1. count all the members with a ‘Start Date’ less than and equal to the given day as long as 'Current Member = “Yes”

2. count all the members with a ‘Start Date’ less than and equal to the given day as long as the ‘End Date’ is greater than the given day

3. Add the two together to give the number of active members on the given day

4, Make sure no other filters mess with the calculation

I won’t bore you with all the DAX formulas I’ve tried. However, any guidance would be greatly appreciated.

Thanks.

[Melissa]

Hi @KieftyKids,

For this type of calculation, you need the “Sales and Orders in Progress” pattern.
Here you’ll find other forum threads dealing with a similar issue and pointing to relevant content here.

Count total number of resources by Financial Year

Hi @tt.xavier welcome to the forum! I’ve created a Date table with a FY year starting on July 1st and Marked that as a date table. [image] Created 2 inactive relationships between the Date table and the tData table. [image] See for more details around this and the limitations, please see: Next Created the following measure: Employee Count = CALCULATE( COUNTROWS ( tData ), FILTER(tData, NOT( tData[Name] ) in {"Vacant"} && tData[StartDate] <= MAX('Date'[Date]) && …

Calculate between time periode

Hi @TrulsB, Welcome to the forum! How to resolve this will all depent on your model, so… It’s important to provide as much context to a question as possible. This includes the data, the model, the formula, the filter context etc. And know that this forum is public. So if your data is considered sensitive - be sure to post a muck-up. Since you have two dates to take into account, I can already suggest looking into this technique.

If you need further assistance please provide a PBIX, thank you.
I hope this is helpful.

[KieftyKids]

Hi Melissa

Thank you! I have struggled to get this formula accurate for a long time. I feel like I’ve found the secret for turning straw into gold.

The way I’ve calculated the formula though is a bit messy. I broke it into two parts and then created a measure to add the two parts together. See below.

I tried unsuccessfully using ‘+’ and ‘&’ to make it all one measure.

Can this be tidied up or is the way I’ve done it okay?

Again, thanks for your help.

Number Members 1 = 
CALCULATE( COUNTROWS( Circles ),
    FILTER( Circles,
        Circles[Circle Open] <= MAX( Dates[Date] ) && 
        Circles[Circle Close] > MIN( Dates[Date] ) ) )


Number Members 2 = 
CALCULATE( COUNTROWS( Circles ),
    FILTER( Circles,
        Circles[Circle Open] <= MAX( Dates[Date] ) && 
        Circles[Circle Current] = "Yes" ) )

Active Members = [Number Members 1] + [Number Members 2]

[EnterpriseDNA]

Thanks for posting your question @KieftyKids To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

[Melissa]

Ok to avoid that you’ll need to do some modelling. I always prefer to push that as far back to the source as I can. Now I don’t know what your source is so I’ll demo this in Power Query.

Here’s the M code depicted below:
Table.AddColumn(#"Changed Type", "PQ EndDate", each if [EndDate] = null and [CurrentMember] ="Yes" then Date.From(DateTime.FixedLocalNow()) else [EndDate], type date )

You can amend this to better fit your specific requirement - for example not to fill in today’s date but the last date from your Dates table, if that better fits your needs.

Now because we have a value for each row the logic becomes simple.

Number of Members = 
CALCULATE( 
    COUNTROWS( Memberships ),
    FILTER( Memberships, 
        Memberships[StartDate] <= MAX( Dates[Date] ) &&
        Memberships[PQ EndDate] >= MIN( Dates[Date] )
    )
)

Number of Members v2 = 
CALCULATE(
     COUNTROWS( Memberships ),
     FILTER( VALUES( Memberships[StartDate] ), Memberships[StartDate] <= MAX( Dates[Date] )),
     FILTER( VALUES( Memberships[PQ EndDate] ), Memberships[PQ EndDate] >= MIN( Dates[Date] ))) 

.
I hope this is helpful.
eDNA - Total of Active Members.pbix (75.0 KB)

[EnterpriseDNA]

Hi @KieftyKids , we’ve noticed that no response has been received from you since the 27th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

[KieftyKids]

Hi Melissa

Thank you.

[KieftyKids]

Hi

The total count is working perfectly. Thanks again.

I’ve now been asked to find how many of each donation group are cancelling each week as a percentage of that donation group. For example if 10 donors in the ‘25 plus’ donations group cancel this week and there were 100 donors in this group at the beginning of the week, then this week 10% of this group have cancelled.

(Management wants to determine if we’re losing more of our valuable donors because of the coronavirus.)

I’ve loaded all the donations into my model (2.2 million) and created a calculated column to determine which group each donor is in. But this grouping will change from week to week as donations are added or donors are lost.

I’m wanting to be able to chart the weekly percentage of donors stopping for each donation group. And I’m stuck.

This is what I have so far.

The groups are:

I have a calculated column to determine what group each donor is in but this is a snapshot as of today:

For this week I can calculate the number of donors in each group we’ve lost but how do I determine how many there were at the beginning of the week. Is it just (those lost + those left)?

For last week I’d need to recalculate the donation count for each donor based on what week it is and when they started giving and if they stopped - I have both dates.

Any guidance would be much appreciated. If my description is unclear please let me know.

Thanks

[Melissa]

Hi @KieftyKids,

Apologies for the late reply!
Instead of a Calculated Column, have you considered implementing this segmentation technique?

Secondary Table Logic Inside Of Power BI Using DAX

.

@KieftyKids

I can calculate the number of donors in each group we’ve lost but how do I determine how many there were at the beginning of the week.

Well if you are doing a Week over Week comparison it would make sense to me to calculate the difference over completed weeks only. So looking at the total from last week vs the total for the week before that. Another approach could be to always compare 7 days with the 7 days before that - but I guess that would depend on the wishes of your Management…

If you need further assistance - just open up a new thread and don’t forget to add a PBIX
That will greatly help in getting a quick response from the many active members on the forum.