Count Distinct Values over Time

Question

Team, I have a sales database where I want to count the distinct orders by either the date they were submitted or&nbsp;the first time they&nbsp;had a&nbsp;posted date. In other words, we have orders, which&nbsp;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&nbsp;ALL somehow?

greg_deckler · Answer

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])

v-chuncz-msft · Answer

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

shelley · Answer

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&nbsp;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.&nbsp;Whether I use:Order Count = DISTINCTCOUNT('Analytics'[OrderNum])Or use Count of OrderNum (Distinct) in the line graph visual, the results are the same:&nbsp;Counts by Month =Mo.&nbsp; &nbsp;Order Count1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; 392&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; 423&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; 334&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23&nbsp;Counts YTD are then showing&nbsp;=Mo.&nbsp; &nbsp;Order Count1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 392&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 773&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1044&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 125However,&nbsp;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,&nbsp;SHOULD be:Mo.&nbsp; &nbsp;Order Count1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 392&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 813&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1144&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 137&nbsp;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]) &amp;&amp; Champ_Calendar_Lookup[Champ_Calendar_Month] &lt;= MAX (Champ_Calendar_Lookup[Champ_Calendar_Month] )))&nbsp;YTD order counts are coming out&nbsp;too low.&nbsp;Maybe I'm just missing something simple?&nbsp;Thanks for any help!

Forum Discussion

Count Distinct Values over Time

3 Replies

Related content

count distinct

Distinct Count

Distinct count

DAX Calculation for Distinct Count

Conditional Distinct Count of Values