Time Intelligence webinar- Evaluation of Year to Date Totals

Anything related to PowerPivot and DAX Formuale
masterelaichi
Posts: 34
Joined: Fri Sep 02, 2016 8:36 am

Time Intelligence webinar- Evaluation of Year to Date Totals

Postby masterelaichi » Mon May 28, 2018 10:04 am

Hi Matt,

This is in reference to your recent time intelligence webinar https://www.youtube.com/watch?v=Ju_JURCHA6o&t=2395s (around the 40 min mark in the video)

I am getting confused around how the filtering works in the evaluation of the Sales YTD code



You explain that the Calculate function "alters" the initial filter context, Calendar[Year] = 2002, Calendar[Month] = June (in your example), and this opens up the entire Calendar table. My confusion is around the MAX function - why is it returning the last date of June,2002? Since the Calendar table has now been "ALL"ed and the entire Calendar table is unfiltered, shouldn't MAX return the last date in the Calendar table? Or is it that the MAX and MIN functions have the power to override the filters that have been set within the Calculate function ?

Also, I have seen a few Cumulative totals which are coded as below



Could you please explain how the filtering and the ALLSELECTED function works here and how it is different from the dax code in your example above?

Thanks

MattAllington
Posts: 967
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Time Intelligence webinar- Evaluation of Year to Date Totals

Postby MattAllington » Mon May 28, 2018 12:49 pm

Very good questions - this shows me that you are indeed thinking carefully about what is happening and that you want to understand. It is not intuitive, which is why it helps to have someone explain it.

I will answer this question in my blog tomorrow morning (about 19 hours from now). https://exceleratorbi.com.au/time-intel ... using-dax/
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

masterelaichi
Posts: 34
Joined: Fri Sep 02, 2016 8:36 am

Re: Time Intelligence webinar- Evaluation of Year to Date Totals

Postby masterelaichi » Mon May 28, 2018 12:57 pm

Thanks Matt. Looking forward to reading your blog

masterelaichi
Posts: 34
Joined: Fri Sep 02, 2016 8:36 am

Re: Time Intelligence webinar- Evaluation of Year to Date Totals

Postby masterelaichi » Mon Jun 04, 2018 2:47 pm

Hi Matt,

Thanks for the blog. To be honest, I am still confounded by this. Everytime I think of the initial filter context affecting only the FILTER function as explained in your blog, the ALL function pops into my head and I think of the entire table and that really messes things up for me

Going to the smallest granular level, as in the image shown below, The initial filter contexts are the ones underlined in red, correct?
Calculate 2.JPG
Calculate 2
Calculate 2.JPG (77.22 KiB) Viewed 503 times


Calendar[ Year] = 2002
Calendar[MonthName] = January
Calendar[Date] = 1/01/2002

which results in $11,930

So, before the Calculate works it's magic, due to the result of the ALL(Calendar) bit of the code in the FILTER functios, for this specific initial filter context acting on the table, is the scrunched up calendar table just this one line item, i.e. 1/01/2002?



And, for the second item, i.e, 02/01/2002, because of the MAX (Date) and MAX (calendar[year]) in the FILTER function, the table remaining is just the two dates as in the image below?

Calculate Data model view.JPG
Calculate Data Model 2 dates
Calculate Data model view.JPG (25.94 KiB) Viewed 503 times


And after this, [Total Sales] is calculated?

Hopefully this makes sense with the screenshots. I am trying to work this out manually (by manually I mean on paper !!)

Thanks

MattAllington
Posts: 967
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Time Intelligence webinar- Evaluation of Year to Date Totals

Postby MattAllington » Mon Jun 04, 2018 4:30 pm

It's not easy, but stick with it. Being able to ask questions definitely helps.

Firstly, I don't know what formula you have for Total Sales YTD with ALL and MAX, but it is not correctly calculating YTD sales. This is the correct formula


Going to the smallest granular level, as in the image shown below, The initial filter contexts are the ones underlined in red, correct?

Assuming you are not talk about the amount $11,930, then yes. Measures are not part of the initial filter context. Only filters coming from the visual are part of the initial filter context.

So, before the Calculate works it's magic, xxxxx, for this specific initial filter context acting on the table, is the scrunched up calendar table just this one line item, i.e. 1/01/2002?


Yes, it is one line item for the first row in the pivot, but this has nothing to do with the ALL(Calendar) bit. The initial filter context is "initial" and it is determined BEFORE the formula is even considered. It wouldn't matter what formula was in this measure - the initial filter context is always the same.

The second parameter of CALCULATE (the FILTER function in this case), operates in the initial filter context as you correctly described above. But ALL(table) doesn't alter the initial filter context. ALL(Table) is an unfiltered copy of the table and it cannot alter the initial filter context. It is simply an unfiltered copy of the table. The only thing that can alter the filter context is CALCULATE (or CALCULATETABLE). The CALCULATE in this function will eventually alter the filter context, but not until FILTER is finished its job.

And, for the second item, i.e, 02/01/2002, because of the MAX (Date) and MAX (calendar[year]) in the FILTER function, the table remaining is just the two dates as in the image below?


I prefer to explain it as follows. Let me refer to the rows in the pivot table by date (I am actually referring to the entire row). Each measure is evaluated independently of each other - you should only consider 1 cell (measure) at a time. I am referring to my version of the formula above.
For 1 Jan, the FILTER function returns a single row table containing 1 Jan
For 2 Jan, the FILTER function returns a two row table containing 1 and 2 Jan
for 3 Jan, the FILTER function returns a three row table containing 1, 2, 3 Jan etc.

If it were not for the ALL() function wrapped around Calendar in line 5, then the formula wouldn't work. In fact without ALL you will get the answer you have shown in your screen shots. The FILTER function needs to work through and UNFILTERED copy of the calendar table to be able and then reapply the new filters it needs to calculate YTD sales. But FILTER is not changing the filter context - it is just working its way through an UNFILTERED copy of the Calendar table in order to work out what to keep and what not to keep. Once it has worked this out, it passes this filtered copy of the table back to the CALCULATE function and asks CALCULATE to change the filter context. THEN the result is generated.

Happy to discuss further if needed.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

masterelaichi
Posts: 34
Joined: Fri Sep 02, 2016 8:36 am

Re: Time Intelligence webinar- Evaluation of Year to Date Totals

Postby masterelaichi » Mon Jun 04, 2018 4:59 pm

Hi Matt,

Sorry, I should have been more clear when I replied. I was just testing some stuff out to see try and see how the result changes with different formulas. I had a few measures without the ALL and MAX functions as well

I will read your explanation a couple of times. Also been reading Rob Collie's and Avi Singh's book. Hopefully that helps

Thanks Matt. I'll try to take it all in and get back to you


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest