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
drew5251
New Member

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

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!

2 REPLIES 2
jthomson
Solution Sage
Solution Sage

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

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.

 

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.