Doing exercise 66 using the Previous Day functions and have come across something "odd"

If you look at the Grand Total Line it seems to be repeating the number for Day 1 when, if you do the math the total should be $597,153 for the month.

The question then is, do I have to write a formula to calculate this total or is there some setting that I am missing.

TIA

## Time Intelligence Function Previous Day

### Re: Time Intelligence Function Previous Day

Hi,

Not necessarily odd, but needs consideration of what a calculation is doing. Each cell in the results, including the Grand Totals are calculated individually. The Grand Total is not the sum of the other cells visible in the pivot.

On the rows above, there is a filter context with the DayNumberOfMonth, therefore it correctly grabs the previous day.

For the Grand Total, there is no context of DayNumberOfMonth, rather, just the MonthName selected. The Day prior to the MonthName of January would be the last day of Dec, and hence, the same result as Jan 1st.

No doubt Matt can explain using "proper" terms, but key is to understand every number in a Power Pivot table is calculated based on the filters applying to that specific cell.

In case you have it, Chapter 7 of Rob Collies' DAX Formulas for Power Pivot covers this, with RULE A: DAX measures are evaluated against the Source Table, NOT the pivot and RULE B: Each measure cell is calculated independently.

This post mentions the golden rules and links to videos, so they might walk through this concept: https://powerpivotpro.com/2009/11/introducing-powerpivot-dax-measures/

I had a flip through Matt's book to see if this concept is covered but did not see it, though Concept 5: Filter Propagation might help understanding of the concepts

Cheers

Phil

Not necessarily odd, but needs consideration of what a calculation is doing. Each cell in the results, including the Grand Totals are calculated individually. The Grand Total is not the sum of the other cells visible in the pivot.

On the rows above, there is a filter context with the DayNumberOfMonth, therefore it correctly grabs the previous day.

For the Grand Total, there is no context of DayNumberOfMonth, rather, just the MonthName selected. The Day prior to the MonthName of January would be the last day of Dec, and hence, the same result as Jan 1st.

No doubt Matt can explain using "proper" terms, but key is to understand every number in a Power Pivot table is calculated based on the filters applying to that specific cell.

In case you have it, Chapter 7 of Rob Collies' DAX Formulas for Power Pivot covers this, with RULE A: DAX measures are evaluated against the Source Table, NOT the pivot and RULE B: Each measure cell is calculated independently.

This post mentions the golden rules and links to videos, so they might walk through this concept: https://powerpivotpro.com/2009/11/introducing-powerpivot-dax-measures/

I had a flip through Matt's book to see if this concept is covered but did not see it, though Concept 5: Filter Propagation might help understanding of the concepts

Cheers

Phil

### Re: Time Intelligence Function Previous Day

Phil;

This makes sense, and I will reference Rob's book to check on this.

Guess that one would have to hide the Grand Total, from a report, as I can see a business person look at that number and mentioning the fact that the "total does not add up".

Cheers

This makes sense, and I will reference Rob's book to check on this.

Guess that one would have to hide the Grand Total, from a report, as I can see a business person look at that number and mentioning the fact that the "total does not add up".

Cheers

Return to “Help with the Book "Learn to Write DAX"”

### Who is online

Users browsing this forum: No registered users and 2 guests