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.

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

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]

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.

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.

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!

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.

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

.

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.