distinct count of customers in rolling 18 months

[wmeyer]

I am trying to count the number of distinct customers each month, for the previous 18 months.

For example: The Count of distinct number of customers from 10/1/15-3/31/17 would = April 2017 # of distinct customers

This would be rolling, so every month it would look at the prior 18 months from the current month. 

This is what i started with, but this is essentially just a formula to find April 2017 number:                                   

CALCULATE (DISTINCTCOUNT([Customer ID]), DATESBETWEEN([Start of Month of Transaction DAte], DATE(2015, 10, 01), DATE(2017, 03, 31)))   This formula didnt work, but it needs to be altered regardless to pull the previous rolling 18 months.

[Ashish_Mathur · Accepted solution]

Hi @wmeyer,

Try this calculated field formula/measure

=CALCULATE(DISTINCTCOUNT([Customer ID]),DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-17),MAX(Calendar[Date])))

In your visual, drag Months from the Calendar table.  There should be a relationship from the Date column of your data table to the date column of your calendar table.

Hope this helps.

[wmeyer]

This formula works to calculate the correct number each month, but the yearly amount is incorrect. I beleive i need to add an All([Year]) or Earlier [Year] but I can't get it to work correctly. 

18 Month Customer Base = CALCULATE(DISTINCTCOUNT('Customer Base'[Customer ID]), DATESBETWEEN('Calendar Table'[Date], DATEADD(STARTOFMONTH('Calendar Table'[Date]),-17,MONTH),STARTOFMONTH('Calendar Table'[Date])))

Monthly Totals: These should be the distinct count of customers that month, and not consider other months in the count. (these are correct in the following table)

Yearly Totals: This should be the distinct count of customers for that year, which would consider all months in that year. (this is incorrect in the table. 5205 is showing, but it should be 10,214)

[Ashish_Mathur]

Hi,

Does this work?

=IF(HASONEVALUE('Calendar Table'[Date]),CALCULATE(DISTINCTCOUNT('Customer Base'[Customer ID]), DATESBETWEEN('Calendar Table'[Date], DATEADD(STARTOFMONTH('Calendar Table'[Date]),-17,MONTH),STARTOFMONTH('Calendar Table'[Date]))),MAXX(VALUES('Calendar table'[Date]),CALCULATE(DISTINCTCOUNT('Customer Base'[Customer ID]), DATESBETWEEN('Calendar Table'[Date], DATEADD(STARTOFMONTH('Calendar Table'[Date]),-17,MONTH),STARTOFMONTH('Calendar Table'[Date])))))

If it does not, then share the link from where i can download your PBI file.

[wmeyer]

My data and table includes data for years 2015-2017 to date. 

From my calculations, the 2017 distinct total should be 148,271 which is quite a bit higher than what is calculating with this formula. I don't quite understand how the formula is working. Is it taking the distinct count of customers across all years 2015-2017? If so, that may be why it is low. I need to make sure it is just taking into consideration the customers in each month for that year, and doing a distinct count of the customers each year.

Also, It seems like a strange coincidence that the totals each each are matching one of the months exactly.

[Ashish_Mathur]

Hi,

For 2017, the distinct count should be 97,969 (the December figure).  Why should it be 148,271?

[wmeyer]

This table is showing the customer base, for each month and each year. The customer base is made up of customers who purchased within the last rolling 18 months. For example: The month 1/1/17 is made up of customers who purchased anywhere between 8/1/15-1/1/17. If the customer purchased in 8/1/15, their 18th month would be 1/1/17, so they would still be in the base. In 2/1/17, they would drop out of the base and be considered a "lost" customer. So the only month in 2017 that that customer would appear is in January.

Therefore, the December figure of 97,969 is not the distinct number of customers for the year, and rather just the number of customers in the base that month. 

Monthly totals should be number of distinct customers in that month, disregarding all other months.

Yearly totals should be number of distinct customers in that year, disregarding all other years. 

[Ashish_Mathur]

Hi,

So in the Sub total row for 2017 (where the figure is 100277), you would want to count those customers who gave you business from the period 1 Aug 2016 to 31 Dec 2017 (18 months).  Am i correct?  If yes, then isn't this the same figure as what you have currently computed for the month of December 2017?

[wmeyer]

The subtotal row for 2017 (where the figure is 100277) should be the disctint count of customers who were in the base at any point in 2017. 

In the attached chart you will see the month the customer had a transaction, and the subsequent months(17) thereafter where they would remain in the base. The Monthly totals are noted in the monthly base total column. You will see in 12/1/17, the monthly total is 3, because Customer B, C, D were in the base for that month. The 2017 total is made up of the customers who were in the base during any point of 2017 (indicated in the Green area). This would give us a total of 4 customers for 2017 because Customers A, B C & D were all in the 2017 base at one point.

[Ashish_Mathur · Accepted solution]

Hi @wmeyer,

Try this calculated field formula/measure

=CALCULATE(DISTINCTCOUNT([Customer ID]),DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-17),MAX(Calendar[Date])))

In your visual, drag Months from the Calendar table.  There should be a relationship from the Date column of your data table to the date column of your calendar table.

Hope this helps.

[Anonymous]

@Ashish_Mathur  This solution using EDate worked very well for me. Thank you for sharing your DAX knowledge.

[Ashish_Mathur]

You are welcome.

[ruban]

Hi Ashish,

The DAX measure that you shared worked perfectly. 

A quick question why did you use 17 instead of 18 in for a rolling 18 month period?

Regards,

Ruban 

[Ashish_Mathur]

Glad to know that.  Go 17 months back and also consider the current month.  That makes it a total of 18 months.

[darraghfarrell]

Hey, I am having an issue with my distinct count value on a rolling 2 months. but as you can see below the formula is not working as we don't get the average of the 2 prior months.

Query : 

Rolling2MonthsDistinctNumberOfUsers = CALCULATE(

DISTINCTCOUNT(tMobileRevenue[Mobile]),

DATESINPERIOD(tMobileRevenue[BillingPeriod],LASTDATE(tMobileRevenue[BillingPeriod]),2,MONTH)

)

 I am expecting the value to be 43857+43186 = 87043. It appears to be giving me the count of the distinct values over the 2 months which is 44607. How do I sum them up?

[Ashish_Mathur]

Hi,

I think the result of 44607 is correct because there would be common users in Dec 2018 and Jan 2019.  Nevertheless, if you want to add the number of users in both months, then try this measure

=[DistinctNumberOfBilledUsers]+CALCULATE([DistinctNumberOfBilledUsers],NEXTMONTH(Calendar[Date]))

There should be a Calendar Table with a relationship from the Date column of the tMobileRevenue table to the Date column of the Calendar Table.  In the Calendar table, write the following calculated column formulas to extract the Year and Month from the: Year = year(calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm").  In your visual, drag YEar and Month from the Calendar Table.

Hope this helps.

[wmeyer]

In your formula, you have "Calendar[Date]" as well as EDATE(Min(Calendar[Date])" and "EDATE(Max(Calendar[Date])"  

I'm not exactly sure which dates I should be putting in each of these. From my question above, do I type in the dates, or choose ym column that references the first or last day of the month for the dates above? 

Sorry if I'm asking the questions in a confusing way, or not making sense. I'm new to Power Bi, so I'm still learning the ropes of formulas and getting the queries set up.

[Ashish_Mathur · Accepted solution]

Hi,

You have to first create a calendar table which shoould have dates ranging from the first date in the date column of your source data table to the last date.  Then create a relatiopnship from the date column of your source data table to your calendar table.  EDATE() function allows you to go back/forward a months that you specify.

[wmeyer]

Thank you for the formula. This is returning a value now, however it is not the value i expected in Column named "Column" on the far right. I pulled this into a visual but it didnt sum like I was hoping. I want it to sum each month, and tell me how many distinct customers i had each month. For example: April might have 43,000 unique customers, which would be pulling the # of unique customers with transaction dates from 10/1/15-3/31/17. Should i be grouping in the query editor before i do this formula? I'm not sure how to get it to calculate the way i need it to. 

 

[Ashish_Mathur]

Hi,

My formula has to be written as a measure - not as a calculated column.

[wmeyer]

Thank you, the measure is returning values now, but the values are much lower than expected and not correct. 

Measure = CALCULATE(DISTINCTCOUNT([Customer ID]),DATESBETWEEN('Customer Base'[End of Month of Transaction],EDATE(MIN('Customer Base'[Start of Month of Transaction DAte]),-17),MAX('Customer Base'[End of Month of Transaction])))

Shouldnt this formula be giving me the number of customer ID's between the end of the month, and the start of the month 17 months prior? Or is there something else I am missing?   Also, what does the MAX function at the end of this formula do?

[Ashish_Mathur]

Hi,

This is not what i suggested earlier.  In my post dates November 17, i had mentioend that you must create a calendar table, then create a relationship.  Furthermore, in the formula, the first input to the DATESBETWEEN() function shoould be CALENDAR[Date].

I can offer more help:

1. If you can share the PBI file
2. Describe your question
3. Show me your expected result.