Daily calculation of distinct count of IDs over the last 14 days

[angeloola]

Have a data table with the following columns:

Date of last status change

Customer ID

City

I need to show a timeline (ideally stacked area chart) for the last 3 months with a daily value for DISTINCT COUNT Customer ID added up over the last 14 calendar days. No column has unique values, 'Date of last status change' doesn't have every date in the calendar. Ultimately I need to be able to filter the daily count by City. 

So, in other words, for every day over the last three months, I need to show a value for 14-day total of distinct count of Customer IDs by City.

I'm new to DAX. Feeling very stuck...

Thanks!

[Phil_Seamark]

Hi @angeloola

Do you have some sample data with a suggestion on what you expect your output should look like for the data?

[angeloola]

Hi! i didn't see a upload tool to share a file, but i've copied some data below.

I want a timeline with a daily value representing the 14-day distinct count of customerID.

Kind of like a moving average, but a moving 'distinct count'. Where i've gotten really stuck is that distinct count has to be a total over the 14 days prior to the date on the timeline. I can do discinct count ID per city per day, but can't get the 'total distinct count over the last 14 days' per city per day.

so a summary table might look something like: 

calendar date -- city -- 14day ID count

The calendar date could have to repeat for every city I guess. I tried calculating a summary table, but could only get date-city-countID. I couldn't figure out how to calculate the 14-day id count based on the calendar date by city.

Please let me know if I can clarify further. This is a problem I'm supposed to solve for a figure i need for work... 😐

Here is a sample of the datatable I am working with: DATA

Thanks!!

[angeloola]

Apologies - the date wasn't formatted correctly.

This link should be better: DATA-datefixed

EDIT: Still stuck. This is what I've got so far: 

[Phil_Seamark]

Hi @angeloola

This might be close.  I have attached a PBIX file with the workings.

Measure = 
    CALCULATE(
        DISTINCTCOUNT('Data'[Customer_ID]),
        ALLSELECTED('Data'),
        DATESBETWEEN(
            'Dates'[Date] ,
            MAX('Dates'[Date])-14 ,
            MAX('Dates'[Date])
        )
    )   

[angeloola]

Thanks so much for taking a stab at it, but if you look at the values, you can see that the totals are divided equally between each city, which is incorrect: 

I came up with the solution below late last night using a measure for distinct count customer ID and a measur for 14-day count of customer ID. Does that make sense to you? I haven't QC'd the data yet, but at least it's giving realistic-looking numbers for each city: 

CustomerID Count = distinctcount(CustomerID)

14-day Count = CALCULATE(Customer[CustomerID Count],FILTER(ALL('Calendar'),
'Calendar'[DayofYear] >= MAX('Calendar'[DayofYear])-14 &&
'Calendar'[DayofYear]<= MAX('Calendar'[DayofYear])))

By the way - how do you upload a file to this forum? I don't have an upload option (just a way to post a link)?

Thanks!!

Angela

[Phil_Seamark]

HI @angeloola

Looks like you are on the right track with your calc.  I hope my suggestions were helpful.

In terms of attaching files, I see the following when I post.  This might be available to me because I'm lucky enough to be in the Super User group (The Datanauts  - https://community.powerbi.com/t5/Community-Blog/Introducing-The-Datanauts/ba-p/362522)