Forum Discussion
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 =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.
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'))RETURNCOUNTROWS(FILTER(__Table,[__Usage]=0 && [months] = 3))Really appreciate the help from you both
8 Replies
- Greg_Deckler
Community Champion
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)) 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'))RETURNCOUNTROWS(FILTER(__Table,[__Usage]=0 && [months] = 3))Really appreciate the help from you both- amitchandak
Super User
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. 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 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))
