Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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.
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.
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.
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
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |