cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wmeyer Regular Visitor
Regular Visitor

distinct count of customers in rolling 18 months

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: distinct count of customers in rolling 18 months

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.

Super User
Super User

Re: distinct count of customers in rolling 18 months

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.

21 REPLIES 21
Highlighted
Super User
Super User

Re: distinct count of customers in rolling 18 months

it would be sth like this:

 

RT =
CALCULATE (
    DISTINCTCOUNT([Customer ID]),
    DATESBETWEEN (
        DimDate[Date],
        DATEADD ( STARTOFMONTH ( DimDate[Date] ), -18, MONTH ),
        STARTOFMONTH ( DimDate[Date] ) - 1
    )
)

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: distinct count of customers in rolling 18 months

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 Regular Visitor
Regular Visitor

Re: distinct count of customers in rolling 18 months

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.

Super User
Super User

Re: distinct count of customers in rolling 18 months

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 Regular Visitor
Regular Visitor

Re: distinct count of customers in rolling 18 months

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. 

 

 

 distinct count.PNG

Super User
Super User

Re: distinct count of customers in rolling 18 months

Hi,

 

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

wmeyer Regular Visitor
Regular Visitor

Re: distinct count of customers in rolling 18 months

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?

Super User
Super User

Re: distinct count of customers in rolling 18 months

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.
wmeyer Regular Visitor
Regular Visitor

Re: distinct count of customers in rolling 18 months

Here is the measure i added, but it is still not working right. I really appreciate your help with this, I'm really struggling!

 

 

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

 

The goal is to get a count of the number of distinct customers each month, for the past rolling 18 months. For example:

between 5/1/16-10/31/17 i would expect a number around 88,501 customers. Currently the formula is throwing an error and not returning anything. 

 

calendar date.PNGThis is my calendar tablecustomer base 1.PNGThese are the fields in my customer table, where I'm, pulling the customer ID's fromcustomer base 2.PNGThese are more of the fields in my customer table, where I'm, pulling the customer ID's from