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
\nDownload File:
\nStart File: http:\/\/ift.tt\/2uqGsLB
\nFinished File: http:\/\/ift.tt\/2vCmsEe
\nEntire page with all Excel Files for All Videos: http:\/\/ift.tt\/1kSFWvs
\nVideo shows how to create DAX Formulas for Change & % Change formulas for both Month Over Month and Year Over Year.
\nExtensive 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.
\nSee how to
\n1. (00:10) Introduction
\n2. (00:40) Look at Change and % Change \u201cShow Values As\u201d Standard PivotTable Calculations and why they are not correct when you have incomplete years, like is often the case with Dashboards & Reports.
\n3. (01:53) First insights about what the DAX formula will be required to doing order to calculate the correct % Change.
\n4. (03:07) Look at Excel Tables and Data Model and first Measure that have already been created
\n5. (03:45) Start Data Model PivotTable
\n6. (04:28) Theory about calculating Sales Last Year
\n7. (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.
\n8. (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\u2019s period).
\n9. (05:42) First attempt at DAX Measure to calculate \u201cSales Last Year\u201d using CALCULATE and SAMEPERIODLASTYEAR.
\n10. (07:11) Diagram of what the SAMPERIODLASTYEAR Function does to the Filter Context Criteria.
\n11. (07:43) Problem with first attempt at \u201cSales Last Year\u201d 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.
\n12. (08:04) Introduction to SUMX (iterates a formula over a table and then adds the results).
\n13. (08:54) Second attempt at \u201cSales Last Year\u201d using SUMX, VALUES and IF DAX Functions
\n14. (09:15) Introduction to VALUES DAX Function (yields a unique list of items for a column in the Current Filter Context).
\n15. (10:51) Error from SAMPERIODLASTYEAR and how to use IF and HASONEVALUE DAX Functions to fix it.
\n16. (12:01) Look at \u201cSales Last Year\u201d Measure in PivotTable and see how it is yielding the correct answer
\n17. (12:35) \u201cSales Lat Year\u201d 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.
\n18. (15:42) Problem with \u201cTotal Sales\u201d formula when using it to calculate the change between this year and last year.
\n19. (16:34) \u201cComparison Total Sales\u201d DAX Formula to calculate correct Total Sales for the Numerator in the \u201c% Change\u201d formula. See the functions, SUMX, VALUES and IF
\n20. (18:07) DAX Formula to calculate the amount of change in Sales from Last Year and This Year. \u201cChange\u201d Formula.
\n21. (19:04) \u201c% Change\u201d DAX Formula to calculate the % change between last year\u2019s period and this year\u2019s period. See DIVIDE DAX Function.
\n22. (20:14) Add New Data to Sales Table to see that formulas will adjust.
\n23. (21:41) Summary<\/p>\n