Distinct Count of Customer for Trailing 12 Month Period by Month

[StephenK]

Hey guys,

I need some direction on how to set up a tricky variation of distinct count by date. I have a long fact table with 10 years of data. Basically it looks like so:

CustomerId	VisitDate	VisitId
SomeNumber	SomeDate	SomeOtherNumber
...	...	...

I need to display the distinct count of customers by month in a column chart. Simple enough. BUT, I need each month to include the distinct count of customerid in the trailing 12 months. So for example January would be distinct count of customers from 1/1/19-1/1/20, Febuary would be 2/1/19-2/1/20. AND, to make it more tricky, ideally you would be able to drill down to the day in the date heirarchy and each day on the chart be it's own 12 month period. (March 3 = 3/3/19-3/3/20, March 4 = 3/4/19-3/4/20).

Hope that makes sense.

Any ideas on how I would set this up?

Thanks!

[Ashish_Mathur]

Hi,

Try this measure

=calculate(distinctcount(Data[CustomerID]),datesbetween(calendar[date],edate(min(calendar[date]),-11),min(calendar[date])))

Please ensure the following:

1. There should be a Calendar Table.  Write calculated column formulas to extract Year, Month Name, Month number.  Sort the Month Name column by Month number
2. Create a relationship from the VisitDate column of your Data Table to the Date column of your Calendar Table
3. To your visual, drag Year, month and date from your Calendar Table

Hope this helps.

[StephenK]

@Ashish_Mathur @dedelman_clng 

Thanks guys! Both formulas work, but I'm noticing some weird behavior when I drill down past the year:

The last four months are way off. My Date Dim and Fact Tables end on the current date so I'm not sure how to address the issue with the last four columns. Any ideas?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[dedelman_clng]

@StephenK  can you share a dummy version of your pbix?

[StephenK · Accepted solution]

@dedelman_clng I have it working with a variation of the other formula you published.

Here's the final formula I used that seems to be working:

RollingCount =

Var _Total = CALCULATE(DISTINCTCOUNT('VisitsFact'[CustomerId]),DATESBETWEEN('Date Dim'[Date],MIN('Date Dim'[Date])-365,MIN('Date Dim'[Date])),FILTER(ALL('Date Dim'),'Date Dim'[Date]<=MAX('Date Dim'[Date]Date]))

Return

IF(MAX('Date Dim'[Date])<=TODAY(),_Total,BLANK())

Thank you for your help!

[dedelman_clng]

Hi @StephenK  -

The general pattern for Rolling 12 months would be

CALCULATE( [Measure], DATESBETWEEN('Date'[Date], MIN('Date'[Date]) - 365, MIN('Date'[Date])))

This should work at every date hierarchy level (1st of the month for month, 1st of the year for year) if you're using a dedicated date table.

Hope this helps

David