Distinct items per day

[gabriela_al1]

Hi all,

I am trying to create a DAX measure that calculcates the new  items per day.That means,for each day,it should check if the item exist in the day before and if not,it is a new item and therefore should be counted.

Important note is that my model is DirectQuery and functions like EARLIER would not work :(.

This is what I have done so far,but this DAX measure is not optimal since there is a lot of itemnumbers that should be evaluated.

VAR CurrentDate = SELECTEDVALUE(All_checks[Date])
VAR PreviousDate = CurrentDate - 1
RETURN
    CALCULATE(
        COUNTROWS(
            FILTER(
                All_checks,
                All_checks[Date] = CurrentDate &&
                NOT (
                    All_checks[ITEMNUMBER] IN
                    CALCULATETABLE (
                        VALUES(All_checks[ITEMNUMBER]),
                        All_checks[Date] = PreviousDate
                    )
                )
            )
        )
    )

Any suggestions would be appreciated.Thank you in advance. 

[Dangar332 · Accepted solution]

hi, @gabriela_al1 

if you need only count of new item then take diference of item number bw present day and previous day

measure =
var a = SELECTEDVALUE(All_checks[Date])
var a = calculate(
          distinctcount(All_checks[ITEMNUMBER]),
           All_checks[date] = a
         )

var b = calculate(
          distinctcount(All_checks[ITEMNUMBER]),
           All_checks[date] = a-1
         )
return
a-b

[some_bih · Accepted solution]

Hi @gabriela_al1 

one of possible solution is to count rows in your Date / Calendar table like with DAX.DO example on link and picture below

Sorry, correct link is this

Your measure could be something like below 

CALCULATE (
COUNTROWS ( 'Date' ),
FILTER ( __table_dates_sales, [Sales] = BLANK () )

adjust your table names and other

[Dangar332 · Accepted solution]

hi, @gabriela_al1 

if you need only count of new item then take diference of item number bw present day and previous day

measure =
var a = SELECTEDVALUE(All_checks[Date])
var a = calculate(
          distinctcount(All_checks[ITEMNUMBER]),
           All_checks[date] = a
         )

var b = calculate(
          distinctcount(All_checks[ITEMNUMBER]),
           All_checks[date] = a-1
         )
return
a-b