Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |