topic Re: Distinct items per day in DAX Commands and Tips

Distinct items per day

gabriela_al1 — Sat, 06 Jan 2024 14:53:15 GMT

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.

Re: Distinct items per day

Dangar332 — Sat, 06 Jan 2024 17:33:39 GMT

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

Re: Distinct items per day

some_bih — Sat, 06 Jan 2024 18:30:39 GMT

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