Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wmeyer
Helper III
Helper III

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
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

23 REPLIES 23

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. 

 

This is my calendar tableThis is my calendar tableThese are the fields in my customer table, where I'm, pulling the customer ID's fromThese are the fields in my customer table, where I'm, pulling the customer ID's fromThese are more of the fields in my customer table, where I'm, pulling the customer ID's fromThese are more of the fields in my customer table, where I'm, pulling the customer ID's from

Hi,

 

Share the link from where i can download your PBI file.  Ensure you have your formula in there.  I will see where the problem is.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ImkeF
Super User
Super User

it would be sth like this:

 

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.