Learn how to create running totals and cumulative totals in Excel using various methods including worksheet formulas, Power Query M code, and DAX calculated columns. Explore techniques for creating overall running totals and running totals by month, while mastering the important skill of creating expandable ranges across different Excel functionalities. Dive into advanced Excel features such as SUMIFS and EOMONTH functions, Power Query custom columns with Table.AddColumn and Table.SelectRows functions, and DAX calculated columns using CALCULATE, SUM, FILTER, EARLIER, and EOMONTH functions. Gain insights into using variables in DAX and understand the nuances of field access operators for efficient data manipulation in Excel.
Overview
Syllabus
) Introduction.
) Excel Worksheet Formula solution using SUMIFS and EOMONTH functions with an Expandable Range. Two formulas: Overall Running Total and Running Totals by Month..
) Look at expandable range in a Worksheet Formula.
) Import Table for both Power Query Solution and DAX Solution using Power Query and the Reference feature..
) Power Query Custom Column with Table.AddColumn Function, Table.SelectRows Function and Two M Code Custom Functions to create expandable ranges that can calculate a Overall Running Total and Running Totals by Month in a Column..
) Lesson about how Custom Columns and M Code Custom Functions work, including the how the keyword “each” is a substitute for an explicitly defined Custom Function..
) Look at expandable range in a Power Query Formula..
) Field Access Operator for Looking up: 1) A Full Column or 2) A Single Item from Each Row..
) DAX Calculated Column with the functions CALCULATE, SUM, FILTER, EARLIER, EOMONTH and a Var Variable to create expandable ranges that can calculate a Overall Running Total and Running Totals by Month in a Column..
) How to use a Variable in DAX. See Var = Mon Return syntax for a Variable in DAX..
) Summary.
Taught by
ExcelIsFun
