How to calculate YTD total by month of subscribers who have been expired greater than 6 months

[Anonymous]

Sorry for the noob question. I've been struggling to figure out how to calculate for each month over a year, the total number of subscribers who have been expired for more than 6 months ago. If Fred had expiry date falling in Jan 2021, for the reporting months from July, August and September he would be counted as one expired subscriber for these months. Also not sure if it makes a difference but some of these expired subscribers may renew their membership. Please help and thanks in advance!

Subscriber name	Expiry Date
Fred	1/1/2021
Harry	15/2/2021
Steve	20/3/2021

Jun	Jul	Aug	Sept	Oct	Nov	Dec	YTD
0	1	2	3

Fred renews his membership in Aug

Jun	Jul	Aug	Sept	Oct	Nov	Dec	YTD
0	1	1	2

[Anonymous]

Thanks Super User for responding to my query!!!

Yes so I want to find whether a client was expired for over six months from today's date. So if I ran my TOTALYTD calculation, it would count 1 towards the total for July. 

I used the Customer and Cal date table drawing a relationship between them on Expiry date and Date.

Customer	Cal
Expiry date	Date
ClientID	Month
	Year
	Monthkey

I created a measure in the Customer table hoping it would filter down the table but it failed because it could not use DATEADD as a TRUE/FALSE expression as a filter. How can I work around this? 

=TOTALYTD(CALCULATE(COUNT(Customer[ClientID]),Customer[StatusDate] < DATEADD(Cal[Date],-3,MONTH)),Cal[Date])

Do you have a calendar table and does it have a  Year*12+MonthNumber column? Did you mean Monthkey, sorry I'm new to DAX.

[Anonymous]

Correction to Measure mentioned above:
=TOTALYTD(CALCULATE(COUNT(Customer[ClientID]),Customer[ExpiryDate] < DATEADD(Cal[Date],-6,MONTH)),Cal[Date])

[lbendlin]

You would use EDATE in such a scenario, not DATEADD.

It is still not clear to me what "6 months" actually means. From your formula it looks like you want day level granularity.  So for example on Aug 15 you want to check if the last renewal was before Feb 15.   Or would you say that the last renewal would have to have been before March 1 ? Before Feb 1?

[Anonymous]

Sorry to cause you confusion. Please disregard whether a client has bothered to renew their membership entirely. I just want to work out if a client has been expired and not renewed for over 6 months eg Fred expired in early Jan, but he decides not to renew his membership indefintely. So when I run the TOTALYTD calculation report Im interested to see a count of each clients who stayed expired for over 6 months. 

I used the EDATE but now I got an error "The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."

=

TOTALYTD(CALCULATE(COUNT(Customers[ClientID]),Customers[ExpiryDate] < EDATE(Cal[Date],-3)),Cal[Date])

[lbendlin]

Use variables.

=

Var e=EDATE(Cal[Date],-6)

Return

TOTALYTD(CALCULATE(COUNT(Customers[ClientID]),Customers[ExpiryDate] < e),Cal[Date])

[Anonymous]

Tbh I'd be really over the moon, if I get the first part solved, I'm struggling with just the basics which is to find out the count of clients who've expired greater than 6 months ago relative to the months in YTD report from Jan-Dec 2021. The formula I used is a stab at the issue but I could be completely wrong. Any help would be amazing!

[lbendlin]

You say you want to calculate "expired for more than 6 months ago" but then you provide subscription timestamps on day level.  Does it matter when in January Fred renewed his subscription, or is it sufficient to ignore the dates and only look at the month number? In which case "more than six months"  would disqualify July but count August, right? Which would mean that Fred would drop off the visual completely?

Do you have a calendar table and does it have a  Year*12+MonthNumber column?