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

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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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