Trying to calculate total member count by week (dynamic)

[IWallis]

I have a data set with the account (member) and the membership end date (pic one), and I would like to create a bar chart with the week number as an x-axis and the total valid member count as Y (pic two), but the problem is if the membership ends I don't want the member to be counted. I created a date table and tried using DAX to do this, but has no luck so far. I would really appreciate your help on this! Thanks!

[Anonymous · Accepted solution]

Hi @IWallis ,

Supposing you have a following calendar table and sample main data.

You don't have to create any relationship between two tables.

Create a measure for counting. This measure calculates the number of AccountID whose membership is still active during the current week.

Count = CALCULATE(COUNT('Table'[AccountID]),FILTER(ALLSELECTED('Table'),[Last_membership_end_date]>=MAX('Calendar'[Date])))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

If in the data, you also have a membership date, then share the download link of the PBI file.