Deprecated: Creation of dynamic property CF\WordPress\DataStore::$logger is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php on line 23
Deprecated: Creation of dynamic property CF\WordPress\Proxy::$pluginAPI is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/Proxy.php on line 31
Deprecated: file_get_contents(): Passing null to parameter #2 ($use_include_path) of type bool is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/codelights-shortcodes-and-widgets/codelights.php on line 20
Warning: session_start(): Session cannot be started after headers have already been sent in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/wordpress/src/class/wordpress.php on line 53
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831
Deprecated: Creation of dynamic property ternplugin\youtube_video::$post is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/plugin/src/class/youtube_video.php on line 64
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831
{"id":742,"date":"2018-08-28T12:56:15","date_gmt":"2018-08-28T19:56:15","guid":{"rendered":"https:\/\/bonbonsguide.com\/wp\/?p=742"},"modified":"2018-12-02T13:14:29","modified_gmt":"2018-12-02T20:14:29","slug":"msptda-10-power-bi-m-code-for-moving-annual-total-mat-custom-function-power-query-custom-column","status":"publish","type":"post","link":"https:\/\/bonbonsguide.com\/wp\/blog\/2018\/08\/msptda-10-power-bi-m-code-for-moving-annual-total-mat-custom-function-power-query-custom-column\/","title":{"rendered":"Power BI M Code for Moving Annual Total (MAT): Custom Function Power Query Custom Column – MSPTDA 10"},"content":{"rendered":"Download Power BI Desktop START File: https:\/\/ift.tt\/2BXmQEV
\nDownload Power BI Desktop FINISHED File: https:\/\/ift.tt\/2MZj7e5
\nDownload pdf Notes about Power Query: https:\/\/ift.tt\/2BXmSg1
\nDownload Excel File with parallel Excel Example: https:\/\/ift.tt\/2ojpNpi
\nAssigned Homework:
\n Download pdf file with homework description: https:\/\/ift.tt\/2PfRSts
\n Example of Finished Homework in Power BI Desktop: https:\/\/ift.tt\/2ojpP0o <\/p>\n
In this Video learn Power Query M Code and Custom Functions to calculate Moving Annual Toatls.
\nTopics:
\n1. (00:15) Introduction
\n2. (01:10) Comment from YouTube that inspired the video. Verbal Description of the Data Model Transformation we want to make, including the Moving Annual Total Calculation.
\n3. (02:07) Thanks to Bill Szysz for Custom Function.
\n4. (02:18) Excel Example of Moving Annual Total
\n5. (03:30) Why Power Query and not Excel or DAX?
\n6. (03:43) Look at final solution and Custom Function to see what we are trying to accomplish, including a method to filter a table with in a Custom Column in Another Table and have the formula see criteria from the the Inner Table and the Outer Table.
\n7. (05:37) Step 1: Look at how we imported files
\n8. (06:07) Step 2: Extract a Sorted Unique List from the source Facet Table. Use Production Operator to get a List, then use the Table.Distinct and Table.Sort functions.
\n9. (07:31) Step 3: M Code to create a Crossjoin of all combinations of Months and Product Names with the steps: Extract Column, Convert to Start of Month, Extract Min and Max Dates, use List.Dates function to create range of dates, then merge using Custom Column to get all combinations of Months and dates.
\n10. (14:39) Step 4: Group BY Date and Product to get Monthly Totals.
\n11. (16:25) Step 5: Create Final Table with the steps: Merge Step 3 and Step 4, Remove Nulls, Add Custom Column to get One Year Back.
\n12. (20:15) Step 5: Sort and how it is different than Excel Sport.
\n13. (21:25) Step 5: Table.Buffer Function allows us to Buffer the Internal Table to prevent a call to the source table for every row in the table.
\n14. (22:22) Step 5: create Custom Column with Function to Calculate Moving Annual Totals (MAT).
\n15. (28:41) Add new data to test if everything updates
\n16. (29:06) Summary<\/p>\n
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.<\/p>\n