Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Customers with 0 usage, Last 'x' months

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 =...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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))