Excel Magic Trick 1424

Average, Moving Average at Different Granularity: DAX or Excel Formulas?

DESCRIPTION

See how to calculate Average, Moving Average at Different Granularity in both DAX and Excel Formulas:
(00:14) Introduction
(00:27) What is Granularity?
(02:15) Look at Data Model and Hide Columns from Client Tool
(03:29) Basic DAX Calculation: Total Profit and Average Profit using the SUM Function and the AVERAGE Function
(04:39) Start PivotTable with DAX Transactional Average for each month. Granularity: Transactional.
(05:22) Excel Spreadsheet formula to calculate Transactional Average for each month using AVERAGEIFS function. Granularity: Transactional.
(06:52) Discuss Filter Context and how it works in the Power Pivot Data Model
(07:41) DAX Formula for Transactional Moving Ave Profit Last 90 Days using CALCULATE and DATESINPERIOD. Granularity: Transactional.
(10:29) Excel Spreadsheet formula to calculate Transactional Moving Ave Profit Last 90 Days using AVERAGEIFS function. Granularity: Transactional.
(11:55) Create Line Chart to plot Month Average and 90 Day Moving Average
(13:08) DAX Formula to calculate Daily Average Profit. Granularity: Daily. See the AVERAGEX Function and the Measure for Total Profit perform a calculation due to Filter Context and Row Context. Discussion of Context Transition with CALCULATE Function converting Row Context into Filter Context.
(16:38) Excel Array Formula to calculate Daily Average Profit. Granularity: Daily. See the functions: AVERAGE, SUMIFS, ROW and INDIRECT. See how to generate (materialise) an entire table of valid dates in a cell formula using an Array Formula. Learn how SUMIFS can add with and OR Logical Test to generate an entire list of Daily Sales in a single cell formula.
(22:59) DAX Formula to calculate Daily Moving Ave Profit Last 90 Days. Granularity: Daily. See the Function DATESINPERIOD to generate a va;id list of dates as a table for the AVERAGEX Function.
(25:18) Excel Array Formula to calculate Daily Moving Ave Profit Last 90 Days. Granularity: Daily. See the functions: AVERAGE, SUMIFS, ROW and INDIRECT.
(26:38) Summary