Count Distinct Values over Time

[Shelley]

Team, I have a sales database where I want to count the distinct orders by either the date they were submitted or the first time they had a posted date. In other words, we have orders, which are submitted and then can have multiple shipments/postings over time - e.g. one order may have a posting in May, August and December, but I only want to count the order once. I'm thinking we could either use the order submit date or the date first posted. How can I do this with DISTINCTCOUNT? Can we do it both ways - one based on the order submit date and the other on the first date posted? Remember, an order may count as distinct in multiple months. How can I make sure it is only counted once? Do I need to use CALCULATE and ALL somehow?

[v-chuncz-msft]

@Shelley,

If you have any specific question on creating the report, share us your table structure and the desired output.

[Greg_Deckler]

Perhaps I am missing something, but if you use DISTINCTCOUNT, it doesn't matter how many times an order appears in the table as long as it has the same order id.

Measure = DISTINCTCOUNT('Distinct'[OrderID])

[Shelley]

@v-chuncz-msftand @Greg_DecklerPerhaps there's something wrong with our data or the formula? I'm trying to calculate a running order count for generic months over time. NOTE: This data is for customers with contracts that all have different start dates, so we had to create a generic calendar with years, quarters and months simply numbered as 1,2,3,4, etc.; hence the filtering below and the reason we cannot use built-in time functions.

 
Whether I use:

Order Count = DISTINCTCOUNT('Analytics'[OrderNum])

Or use Count of OrderNum (Distinct) in the line graph visual, the results are the same:

Counts by Month =

Mo.   Order Count

1              39

2              42

3              33

4              23

Counts YTD are then showing =

Mo.   Order Count

1              39

2              77

3              104

4              125

However, if the Monthly counts are correct, then the YTD counts are incorrect (or vice versa - if YTD counts are correct, then monthly are incorrect). For example, if monthly counts calculated above are correct, then YTD, SHOULD be:

Mo.   Order Count

1              39

2              81

3              114

4              137

Here's the formula I'm using for YTD Count:

Count Orders YTD = CALCULATE ((DISTINCTCOUNT('Analytics'[OrderNum])), FILTER (ALL(Champ_Calendar_Lookup), Champ_Calendar_Lookup[Champ_Calendar_Year] = MAX (Champ_Calendar_Lookup[Champ_Calendar_Year]) && Champ_Calendar_Lookup[Champ_Calendar_Month] <= MAX (Champ_Calendar_Lookup[Champ_Calendar_Month] )))

YTD order counts are coming out too low.

Maybe I'm just missing something simple?

Thanks for any help!