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

Do you have a date dimension? – Davide Bacci Sep 21 '22 at 11:58 — Davide Bacci, Sep 21 '22 at 11:58
@David, yes, I have a calendar dimension. – bjnr Sep 21 '22 at 12:03 — bjnr, Sep 21 '22 at 12:03

Ozan Sen · Answer 1 · 2022-09-21T13:50:18.297

1

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

edited Sep 21 '22 at 13:50

answered Sep 21 '22 at 12:29

Ozan Sen

2,477
2
4
16

1

Interesting, SUMX(..., 1), thanks. Although, VALUES is considering current filter context, which might be different than current year. – bjnr Sep 21 '22 at 12:49
1

Yes, You are right. VALUES always takes current visible filter context into account. If you want a specific year(current year etc..) you can use a year slicer, and pick the year you want. Or inside measure, define a filter. Like this: Table[Year] = SELECTEDVALUE(Table[Year]) – Ozan Sen Sep 21 '22 at 13:02
1

I think ALL should be used instead of VALUES. But remains question 2... – bjnr Sep 21 '22 at 13:05
1

Also code provided by @David below is really fast as he used golden function 'summarize'. – Ozan Sen Sep 21 '22 at 13:08
If you use all as table function, the result will be the same, I think. In terms of performance-wise, there won't be much difference; but you need to test and see it yourself. – Ozan Sen Sep 21 '22 at 13:10
1

did you see the second question in my post? – bjnr Sep 21 '22 at 13:12
Now, I saw. I think You can solve it as I gave you the hint above. If you can't, I will be glad to recommend a solution. – Ozan Sen Sep 21 '22 at 13:14
I added a solution for question 2. – bjnr Sep 21 '22 at 13:33
1

Good Solution- Time Intelligence! It adds the cumulative total. I also added as an alternative in my post. – Ozan Sen Sep 21 '22 at 13:51

score 1 · Answer 2 · answered Sep 21 '22 at 12:30

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 Answers2