Excel Magic Trick 1449: Why Standard PivotTable Show Values As % Change Does Not Work for Incomplete Years and How To Fix It with DAX Formulas
Download File:
Start File: http://ift.tt/2uqGsLB
Finished File: http://ift.tt/2vCmsEe
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Video shows how to create DAX Formulas for Change & % Change formulas for both Month Over Month and Year Over Year.
Extensive discussion about the hows and whys of how the formula calculates. In particular we discuss how Filter Context and Row Context Work together in SUMX to get the correct amount for each time period.
See how to
1. (00:10) Introduction
2. (00:40) Look at Change and % Change “Show Values As” Standard PivotTable Calculations and why they are not correct when you have incomplete years, like is often the case with Dashboards & Reports.
3. (01:53) First insights about what the DAX formula will be required to doing order to calculate the correct % Change.
4. (03:07) Look at Excel Tables and Data Model and first Measure that have already been created
5. (03:45) Start Data Model PivotTable
6. (04:28) Theory about calculating Sales Last Year
7. (04:43) Discussion about Filter Context and how DAX Measures evaluate / calculate. Discussion and Visuals about how Criteria in Row Area of PivotTable (also known as Filter Context) flows across relationships to create filter Sales Tables that have fewer rows than the original data set so that DAX Formulas can calculate more quickly on Big Data.
8. (05:23) Introduce CALCULATE DAX Function (functions that can change the Filter Context) and SAMEPERIODLASTYEAR DAX Function (generates a valid list of dates from last year’s period).
9. (05:42) First attempt at DAX Measure to calculate “Sales Last Year” using CALCULATE and SAMEPERIODLASTYEAR.
10. (07:11) Diagram of what the SAMPERIODLASTYEAR Function does to the Filter Context Criteria.
11. (07:43) Problem with first attempt at “Sales Last Year” DAX Measure: It shows values for periods that have no current sales, and Total for the last year in data set adds all 12 months from previous period.
12. (08:04) Introduction to SUMX (iterates a formula over a table and then adds the results).
13. (08:54) Second attempt at “Sales Last Year” using SUMX, VALUES and IF DAX Functions
14. (09:15) Introduction to VALUES DAX Function (yields a unique list of items for a column in the Current Filter Context).
15. (10:51) Error from SAMPERIODLASTYEAR and how to use IF and HASONEVALUE DAX Functions to fix it.
16. (12:01) Look at “Sales Last Year” Measure in PivotTable and see how it is yielding the correct answer
17. (12:35) “Sales Lat Year” Formula: Extended discussion with Visuals and Diagrams to understand how VALUES generates a different size table in each cell in the PivotTable and how SUMX iterates over the VALUES generated table to calculate the correct answer in each cell in the PivotTable.
18. (15:42) Problem with “Total Sales” formula when using it to calculate the change between this year and last year.
19. (16:34) “Comparison Total Sales” DAX Formula to calculate correct Total Sales for the Numerator in the “% Change” formula. See the functions, SUMX, VALUES and IF
20. (18:07) DAX Formula to calculate the amount of change in Sales from Last Year and This Year. “Change” Formula.
21. (19:04) “% Change” DAX Formula to calculate the % change between last year’s period and this year’s period. See DIVIDE DAX Function.
22. (20:14) Add New Data to Sales Table to see that formulas will adjust.
23. (21:41) Summary

View on YouTube