Time Intelligence Function Previous Day

A dedicated forum to help people when working through the book "Learn to Write DAX"
peterhof3
Posts: 7
Joined: Wed Jun 21, 2017 3:36 am

Time Intelligence Function Previous Day

Postby peterhof3 » Thu Jul 06, 2017 12:44 am

Doing exercise 66 using the Previous Day functions and have come across something "odd"
Matt Previous Day.PNG
Matt Previous Day.PNG (16.45 KiB) Viewed 563 times


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

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Time Intelligence Function Previous Day

Postby PhilC » Thu Jul 06, 2017 1:57 pm

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

peterhof3
Posts: 7
Joined: Wed Jun 21, 2017 3:36 am

Re: Time Intelligence Function Previous Day

Postby peterhof3 » Fri Jul 07, 2017 12:01 am

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


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

Who is online

Users browsing this forum: No registered users and 1 guest