Customers with 0 usage, Last 'x' months

[Anonymous]

Hi all,

I am looking to create a formula that counts (or flags) the number of customers that have had 0 usage within the last x months (where x can be changed if needed - for this example let x =2).

Here is my dataset.  If a customer does not have a contract anymore, then they simply do not appear for that particular month.  So if I wanted to return the count of customers that have had no usage in the last 2 months, I'd expect to see a count of 1 as only Sarah hasn't had any usage, whilst having an active contract.  Steve and Mary would not be counted as are not an active customer or have gaps.

Customer	Date	Usage
Bill	Jan-20	100
Steve	Jan-20	0
Sarah	Jan-20	10
Mary	Jan-20	10
Jane	Jan-20	10
Elizabeth	Jan-20	25
Bill	Feb-20	50
Steve	Feb-20	0
Sarah	Feb-20	0
Mary	Feb-20	0
Jane	Feb-20	0
Elizabeth	Feb-20	10
Bill	Mar-20	50
Sarah	Mar-20	0
Mary	Mar-20	0
Jane	Mar-20	10
Bill	Apr-20	0
Sarah	Apr-20	0
Jane	Apr-20	0
Bill	May-20	10
Sarah	May-20	0
Mary	May-20	0
Jane	May-20	100
Bill	May-20	0
Sarah	May-20	0
Mary	May-20	0
Jane	May-20	100

I have been going round in circles a little bit with this one, especially looking at the lapsed customers DAX pattern, but the 'inactive' customers seem to be the sticking point. 

Any help would be much appreciated.

[Anonymous · Accepted solution]

Thank you both.  I think I have got the answer.

Using @Greg_Deckler's solution as the starting point and an idea from @amitchandak., I have the following, which gives me the expected result.

GregSolution =

VAR __Table =

SUMMARIZE(

FILTER('Phone Usage','Phone Usage'[Date]<=DATE(2020,5,1) && [Date]>=EOMONTH(DATE(2020,5,1),-2)+1),

[Customer],

"__Usage",SUM([Usage]), "months", COUNTROWS('Phone Usage')

)

RETURN

COUNTROWS(FILTER(__Table,[__Usage]=0 && [months] = 3))

Really appreciate the help from you both

[Anonymous]

Sure no problem.  Here is the summarised table.  Mary in April has had a break in her contract and as such she has only has 1 consecutive month of 0 usage.  The count should return 1 as only Sarah in this example has had 3 consecutive months of (non broken) 0 usage.

[Greg_Deckler]

@Anonymous So like this? See attached PBIX file below sig. Page 15.

Measure 15 = 
  VAR __Date = MAX('Table (15)'[Date])
  VAR __Users3Months = SELECTCOLUMNS(FILTER('Table (15)',[Date]>=EOMONTH(__Date,-2)+1 && [Date]<=EOMONTH(__Date,-1)),"Customer",[Customer])
  VAR __Table = 
    SUMMARIZE(
      FILTER('Table (15)',[Date]<=__Date && [Date]>=EOMONTH(__Date,-3)+1),
      [Customer],
      "__Usage",SUM([Usage])
    )
  VAR __Table1 = FILTER(__Table,[Customer] IN __Users3Months)
RETURN
  COUNTROWS(FILTER(__Table1,[__Usage]=0))

[Anonymous]

Thank you both for the quick replies, hugely appreciated.

Funnily enough, the issue with both solutions, is that they both return a value of 2, as they are including Mary, but she didn't have a record in April and this should have 'reset' the count for her.  This is the same issue that I was hitting as I don't want to count the months that are blank, I want to have 'x' consecutive months where the usage is 0.

Hope that makes sense.

[amitchandak]

@Anonymous , distinct count of the month and then count =3, if it rolling three, something like that?

[Greg_Deckler]

@Anonymous No, not entirely, please post expected output and why.

[Greg_Deckler]

@Anonymous - Maybe:

Measure = 
  VAR __Table = 
    SUMMARIZE(
      FILTER('Table',[Date]<=TODAY() && [Date]>=EOMONTH(TODAY(),-3)+1),
      [Customer],
      "__Usage",SUM([Usage])
    )
RETURN
  COUNTROWS(FILTER(__Table,[__Usage]=0))

[amitchandak]

@Anonymous , With a date table.

Now in you case have Date like

Date = "01-" & [Month] // Mark data type as date

example

Rolling 3 have Usages= CALCULATE(sum(Table[Usages]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-3,MONTH))
Rolling 3 have Not Usages = if(CALCULATE(sum(Table[Usages]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-3,MONTH)) +0>0,blank(),1)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.