Download File:
Start File: https://excelisfun.net/files/EMT1424Start.xlsx
Finished File: https://excelisfun.net/files/EMT1424Finished.xlsx
Entire page with all Excel Files for All Videos: https://excelisfun.net
See how to calculate Average, Moving Average at Different Granularity in both DAX and Excel Formulas:
1. (00:14) Introduction
2. (00:27) What is Granularity?
3. (02:15) Look at Data Model and Hide Columns from Client Tool
4. (03:29) Basic DAX Calculation: Total Profit and Average Profit using the SUM Function and the AVERAGE Function
5. (04:39) Start PivotTable with DAX Transactional Average for each month. Granularity: Transactional.
6. (05:22) Excel Spreadsheet formula to calculate Transactional Average for each month using AVERAGEIFS function. Granularity: Transactional.
7. (06:52) Discuss Filter Context and how it works in the Power Pivot Data Model
8. (07:41) DAX Formula for Transactional Moving Ave Profit Last 90 Days using CALCULATE and DATESINPERIOD. Granularity: Transactional.
9. (10:29) Excel Spreadsheet formula to calculate Transactional Moving Ave Profit Last 90 Days using AVERAGEIFS function. Granularity: Transactional.
10. (11:55) Create Line Chart to plot Month Average and 90 Day Moving Average
11. (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.
12. (16:38) Excel Array Formula to calculate Daily Average Profit. Granularity: Daily. See the functions: AVERAGE, SUMIFS, ROW and INDIRECT. See how to generate (materialize) 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.
13. (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.
14. (25:18) Excel Array Formula to calculate Daily Moving Ave Profit Last 90 Days. Granularity: Daily. See the functions: AVERAGE, SUMIFS, ROW and INDIRECT.
15. (26:38) Summary