How to find distinct counts of an item and compare overlapping time periods

[drew5251]

Hi there,

I'm new to the forum.  I have a business user who wants to create a measure that shows the distinct count of an item over a series of time periods.  

He wants to be able to for each date, go and look at the past fifteen months and then show how many distinct customers there were there.  

I can create a date range that changes using the relative dates of today.  But I don't want to see just one range.  I want to see every range.  So I'd like to see 5/17/18, 5/16/18 etc. going back to the beginning of my dataset and then the number of distinct customers for the prior period where that period begins 15 months prior to the given date and ends on that date.

Here's the range that is working:

Distinct Customers over DateRange = CALCULATE( [Distinct Customers], DATESBETWEEN([date], TODAY()-5, TODAY()-1))

One problem I have is that there are gaps in the date going back.  I want to be able to show dates all the way back to 2014, but there is only one day listed in the [date] column for 2014!

Hope this makes sense.  I appreciate any help!

[jthomson]

Have you tried creating a date table to remove the gaps you have?

[drew5251]

I tried initally, but I think I had blank rows that came in.  This caused an error when I tried to create a relationship.  I have fixed that now.