topic looping through past dates in DAX Commands and Tips

looping through past dates

Delopiero84 — Mon, 11 Apr 2022 19:47:40 GMT

Hellou guys,

I have problem with example below. I am in some DAX course with exercizes , but even with this result I cant get red highlighted part.

Task was, identify and count new customers in specific day(means, they had purchase first time that date).

Cant get red highlighted part. According my backround from excel I suppose that there is loop via customer key- then inside that is comparing (another loop, but via dates) if ANY of their purchases was before current date of visual(in this case for example first line-1/5/2011..

So their are two loops basicaly..Is that correct thinking, pls?And if so, what exactly is triggering those loops?

SOLUTION:

Re: looping through past dates

Whitewater100 — Mon, 11 Apr 2022 20:53:47 GMT

Hi:

I can try to explain. Your table or matrix looks like it is on a daily basis. So that sets the filter context. Because you are analyzing by date, in this case MIN or MAX or AVG all are the same thing.

SELECTCOLUMNS is an iterator, so it will go row by row and you are basically renaming a column(s). Here you are renaming InternetSales[Customer Key] to "Customer Key". But before doing this (FILTER FIRST-EVALUATE SECOND) you are Filltering all the order dates(which have customer keys) in the internet sales table for the time period before any date you examine on your date visual.. (Since your currentdate variable is the same as the date you are looking at in your table.) So on Jan 7th you are looking at any date before Jan 7th to see if any customer key comes up to satisfy your variable named Customers2. This is producing a list of every customer who bought before Jan7th.

Your first variable Customers1 is all the customers buying on jan7th.

So your result is figuring any customer who bought on jan7th(Customer1) that had not bought before this date. This makes them new.

I hope this makes some sense. If your table was monthly, Cusomers1 would be any customer buying in this month and Customers2 would be any customers buying before this month.

Re: looping through past dates

tamerj1 — Mon, 11 Apr 2022 20:55:00 GMT

Hi Delopiero84

you should manipulate the Date filter context instead.

New customer count = VAR CurrentDate = MIN ( 'Date'[Date Key] ) VAR CurrentCustomers = VALUES ( 'Internet Sales'[Customer Key] ) VAR OldCustomers = CALCULATETABLE ( VALUES ( 'Internet Sales'[Customer Key] ), REMOVEFILTERS ( 'Date' ), 'Date'[Date Key] <= CurrentDate ) RETURN COUNTROWS ( EXCEPT ( CurrentCustomers, OldCustomers ) )