topic Re: Multiple date filters in calculatetable in DAX Commands and Tips

Multiple date filters in calculatetable

Anonymous — Mon, 09 Aug 2021 08:02:51 GMT

Hi,
I saw this code snippet in a wokrshop held by Jeffrey Wang. The snippet defines a measure to calculate the number of returning customers by date, in a very standard data model with sales, products and customers.

-----------------------

# NEW customers =

VAR vCustomersThisPeriod= VALUES(Appended_actuals[CustomerID])

var vCurrentdate = MIN('Date'[Date])

var VCustomersPreviousPeriod = CALCULATETABLE(VALUES(Sales[CustomerID]),
FILTER(ALL('Date'[Date]),'Date'[Date]<vCurrentdate),ALL('Date'[Date]))

Return COUNTROWS(EXCEPT(vCustomersThisPeriod,VCustomersPreviousPeriod))

-----------------------

My question is , why is there a need for the second ALL('Date'[Date]) in VCustomersPreviousPeriod?
Doesn't the first filter already accomplish the task?
Thanks a lot for the answers.

Re: Multiple date filters in calculatetable

amitchandak — Mon, 09 Aug 2021 12:17:10 GMT

Anonymous , I usually follow a measure approch

Current = sum(Table[Sales])

past =

Var _min = minx(allselected('Date'), Date[Date])

return

calculate(sum(Table[Sales]), filter(all('Date'), Date[Date] <_min)

new = countx(values(customer[Customer]), if(not(isblank([Current])) && isblank([Past]) , [Customer], bank()))

Re: Multiple date filters in calculatetable

Anonymous — Mon, 09 Aug 2021 12:23:34 GMT

Hi amitchandak , thanks a lot for taking the time to answer, but I am not sure it responds to the question i had. In any case, it is much appreciated.

Re: Multiple date filters in calculatetable

Anonymous — Mon, 09 Aug 2021 14:40:03 GMT

Good spot! This ALL(...) is not necessary. It serves no purpose, even though it's not harmful, either. The FILTER overwrites any filter present on the column anyway, so removing any filters via ALL is redundant.

Re: Multiple date filters in calculatetable

Anonymous — Mon, 09 Aug 2021 14:57:17 GMT

Hey daxer, thanks for the explanation!