Learn about DAX Functions: CALCULATE, SAMPERIODLASTYEAR, TOTALYTD, DATEADD, IF, AND, HASONEVALUE and More!
Learn How to Define a Variable in a DAX Measure using VAR and RETURN Keywords
Learn how to build a complete Data Model and DAX Formulas without Power Pivot in Excel 2016
Download File:
Start File: http://ift.tt/2unJw83
Finished File: http://ift.tt/2u7PyOO
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Topics in This Video:
1. (00:04) How to Download Files.
2. (00:14) Introduction to building Data Model with Relationships and DAX Formulas without buying Power Pivot in Excel 2016.
3. (02:47) Import Tables and Build Relationships using the Relationship button in the Data Ribbon Tab.
4. (04:45) Build Data Table in Excel, including TEXT Function formula to get Months to Sort Correctly in a Data Model PivotTable, without using the Sort By Column feature. See the Fill Series Trick to get a complete list of dates, see the functions YEAR, MONTH and TEXT.
5. (09:23) Add Relationship to Date Table.
6. (09:43) Start building Data Model PivotTable and notice a problem with month names not sorting correctly. Then fix it with the TEXT Function using custom Number Formatting “mm – mmm”.
7. (12:04) Create first DAX Measure (Formula) to add Total Sales. See SUM Function and how to add Number Formatting to Measure.
8. (13:42) What NOT to do when you are hacking the Data Model. Do not drag Sales or Date Fields to Values Area or Row Area of Pivot Table. These are called Implicit Measures and they are not efficient.
9. (14:47) Discuss Formula to get Last Year’s Sales.
10. (15:04) Discussion about Filter Context and how DAX Measures calculate or evaluate to get the correct answer.
11. (16:28) Create Second Measure (Formula) to get Last Year’s Sales using the CALCULATE and SAMEPERIODLASTYEAR DAX Functions.
12. (19:22) How to hide Grand Total Formula with IF and HASONEVALUE DAX Functions. We use HASONEVALUE Function on the Year Column.
13. (22:27) Formula to calculate Difference From Last Year using Two Previously Defined Measures and the IF Function.
14. (24:00) Third DAX Measure (Formula) for Running Total using TOTALYTD, IF and HASONEVALUE function on the Month Column.
15. (27:28) Fourth DAX Measure (Formula) for Difference from Last Month using CALCULATE, DATEADD, IF, HASONEVALUE, and AND DAX Functions. Also see Variable in DAX.
16. (29:50) Create Variable in Excel 2016 DAX Function language using the VAR and RETURN Keywords.
17. (34:26) Summary
Excel Power Pivot, How to Get Power Pivot For Free, How Do I Get Power Pivot?, Build Complete Data Model without Power Pivot, DAX Formulas in Excel, Excel 2016, Highline College, Mike Girvin, excelisfun, Excel Magic Trick, Data Model PivotTables, Excel 2016 Relationships, Relationships in Excel, Multiple Tables in PivotTable Field List, CALCULATE, SAMPERIODLASTYEAR, TOTALYTD, DATEADD, IF, AND, HASONEVALUE, DAX Measures, DAX Formulas, How to Create Variable in DAX in Excel 2016, VAR in Excel, DAX Variable in Excel