Tag: DAX

Excel Magic Trick 1453 Array Formula Count Customer Totals Between Upper Lower Limits, Each Month

Posted on by Annie D

Count Customer Totals Between Upper & Lower Limits For Each Month using an Excel Array Formula and the functions SUMPRODUCT and SUMIFS. Create a Cross Tabulated Frequency Distribution that shows a count of Customer Totals for each month and each Sales Category.
Download File: http://ift.tt/2vP7LBc
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs

Related Videos:
Excel Magic Trick 1453 Array Formula Count Customer Totals Between Upper Lower Limits, Each Month
Excel Magic Trick 1454 DAX Measure Count Customer Totals Between Upper Lower Limits, Each Month

View on YouTube

Excel Magic Trick 1451: Hack Data Model, Date Table & DAX Measures WITHOUT buying Power Pivot

Posted on by Annie D

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

View on YouTube

Excel Magic Trick 1449: DAX Measure % Change MOM & YOY Formulas For Incomplete Years

Posted on by Annie D

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
Download File:
Start File: http://ift.tt/2uqGsLB
Finished File: http://ift.tt/2vCmsEe
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Video shows how to create DAX Formulas for Change & % Change formulas for both Month Over Month and Year Over Year.
Extensive 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.
See how to
1. (00:10) Introduction
2. (00:40) Look at Change and % Change “Show Values As” Standard PivotTable Calculations and why they are not correct when you have incomplete years, like is often the case with Dashboards & Reports.
3. (01:53) First insights about what the DAX formula will be required to doing order to calculate the correct % Change.
4. (03:07) Look at Excel Tables and Data Model and first Measure that have already been created
5. (03:45) Start Data Model PivotTable
6. (04:28) Theory about calculating Sales Last Year
7. (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.
8. (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’s period).
9. (05:42) First attempt at DAX Measure to calculate “Sales Last Year” using CALCULATE and SAMEPERIODLASTYEAR.
10. (07:11) Diagram of what the SAMPERIODLASTYEAR Function does to the Filter Context Criteria.
11. (07:43) Problem with first attempt at “Sales Last Year” 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.
12. (08:04) Introduction to SUMX (iterates a formula over a table and then adds the results).
13. (08:54) Second attempt at “Sales Last Year” using SUMX, VALUES and IF DAX Functions
14. (09:15) Introduction to VALUES DAX Function (yields a unique list of items for a column in the Current Filter Context).
15. (10:51) Error from SAMPERIODLASTYEAR and how to use IF and HASONEVALUE DAX Functions to fix it.
16. (12:01) Look at “Sales Last Year” Measure in PivotTable and see how it is yielding the correct answer
17. (12:35) “Sales Lat Year” 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.
18. (15:42) Problem with “Total Sales” formula when using it to calculate the change between this year and last year.
19. (16:34) “Comparison Total Sales” DAX Formula to calculate correct Total Sales for the Numerator in the “% Change” formula. See the functions, SUMX, VALUES and IF
20. (18:07) DAX Formula to calculate the amount of change in Sales from Last Year and This Year. “Change” Formula.
21. (19:04) “% Change” DAX Formula to calculate the % change between last year’s period and this year’s period. See DIVIDE DAX Function.
22. (20:14) Add New Data to Sales Table to see that formulas will adjust.
23. (21:41) Summary

View on YouTube

Power BI for Accountants: Complete Introduction to Power BI Desktop to Build Reports & Dashboards

Posted on by Annie D

Complete Introduction to Power BI Desktop. This video will teach you about how to use Power BI Desktop to clean, transform data and make a Dashboard to Monitor Sales. Take multiple Text files and import them into a single table which can be used for Reports and Dashboards and can be updated easily when new data arrives.
Download Files:
Zipped Folder file Text Files: http://ift.tt/2uczRVa
Excel File: http://ift.tt/2vEnQFN
Finished File: http://ift.tt/2uc5gXA
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Excel Accounting Seminar: 2017 WAATP Tacoma Accounting Conferences at La Quinta Inn, July 12
This is the third video in a series of three videos.
Topics in Video:
1. (00:10) Download Files
2. (00:24) Video Introduction and review of Topics covered in video
3. (03:13) Introduction to Power BI Desktop
4. (04:15) Overview of process of using Power BI Desktop to build our specific Reports and Dashboards
5. (05:09) Open and Save Power BI File
6. (06:28) Import Excel dProduct Lookup Tables
7. (08:05) Import and Append Multiple Text Files with Sales Data
8. (15:11) Clean Data
9. (16:42) Discussion of Relationships and why they are important
10. (17:58) Create Calendar Table using DAX CALENDAR Table Function
11. (21:27) Create Calculated Columns with DAX Functions: MONTH, FORMAT and YEAR
12. (21:54) Create Relationships and discuss more about Relationships
13. (26:25) Save As to save Power BI File
14. (26:54) Create Cross Tabulated Power BI Report
15. (28:11) Show Values As % of Column Total
16. (30:35) Create Gross Profit Dash Board
17. (31:29) DAX Measure for Gross Profit %
18. (34:24) Column and Bar Chart as Slicer for Gross Profit Dashboard
19. (36:13) Dashboard Interactivity
20. (38:00) Add New Data And Watch Dashboard Update
21. (39:53) Conclusion & Summary
Event Sponsored by:
Puget Sound/Tacoma Chapter WAATP
Location:
La Quinta Inn
1425 E 27th St,
Tacoma, WA 98421
Event Details:
Wednesday, July 12 at 8 AM

View on YouTube

Excel Magic Trick 1447: DAX DISTINCTCOUNT & CONCATENATEX to Count & List Stores Visited

Posted on by Annie D

Download File:
Start File: http://ift.tt/2tSGcWC
Finished File: http://ift.tt/2tSGcWC
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
See how to use the : DAX functions DISTINCTCOUNT & CONCATENATEX to Count & List Stores that Customers Have Visited.

View on YouTube