Calculate number of sales days in DAX

Question

1/ How to calculate the number of sales days (= number of days at least one sale has been done)?

So far, I created this measure, but I'd like to know if another more optimal solution exists for a large sales table (1b+ records):

Sales[No of Sales Days] :=
        CALCULATE (
            DISTINCTCOUNT ( Sales[Date Key] )
        )

2/ How to calculate number of sales days in the current year? Is anything more optimal than this?

Sales[No of Sales Days YTD] :=
    CALCULATE ( 
        [No of Sales Days], 
        DATESYTD ( 'Calendar'[Calendar Date] )
    )

Ozan Sen · Answer

Yes, There is a better code as DISTINCTCOUNT is a problematic structure in DAX. Please try this, and confirm the results back to me!

Q1)

Sales[No of Sales Days] :=
CALCULATE ( SUMX ( VALUES ( Sales[Date Key] ), 1 ) )

Q2)

Sales[No of Sales Days YTD] :=
VAR CurrentYear =
    YEAR ( TODAY () )
RETURN
    CALCULATE (
        [No of Sales Days],
        FILTER ( ALL ( Calendar[Year] ), Calendar[Year] = CurrentYear )
    )

userjpYe1 · Answer

It really depends on your model. There are a few options which have been written about.

https://gorilla.bi/dax/optimize-distinctcount/ https://www.sqlbi.com/articles/analyzing-distinctcount-performance-in-dax/

I would try a few different options e.g.:

Sales[No of Sales Days] =
   COUNTROWS(
      SUMMARIZE(
          ‘Sales’
          ,’Sales[Date Key]
          )
      )

Calculate number of sales days in DAX

2 Answers