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
Time Intelligence webinar Evaluation of Year to Date Totals

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

 Posts: 1019
 Joined: Sun May 04, 2014 4:01 pm
 Location: Sydney, Australia
Re: Time Intelligence webinar Evaluation of Year to Date Totals
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/timeintel ... usingdax/
I will answer this question in my blog tomorrow morning (about 19 hours from now). https://exceleratorbi.com.au/timeintel ... usingdax/
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training

 Posts: 36
 Joined: Fri Sep 02, 2016 8:36 am
Re: Time Intelligence webinar Evaluation of Year to Date Totals
Thanks Matt. Looking forward to reading your blog

 Posts: 36
 Joined: Fri Sep 02, 2016 8:36 am
Re: Time Intelligence webinar Evaluation of Year to Date Totals
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?
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?
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
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?
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?
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

 Posts: 1019
 Joined: Sun May 04, 2014 4:01 pm
 Location: Sydney, Australia
Re: Time Intelligence webinar Evaluation of Year to Date Totals
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
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.
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.
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.
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
Power BI Training

 Posts: 36
 Joined: Fri Sep 02, 2016 8:36 am
Re: Time Intelligence webinar Evaluation of Year to Date Totals
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
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
Who is online
Users browsing this forum: No registered users and 2 guests