MSPTDA 29: Order & Ship Date in Fact Table? Side-By-Side & Cross Tab Reports with Worksheet Formulas

preview_player
Показать описание

In this video learn about how to create a Side-By-Side and Cross Tabulated Report for Total Sales for Order Date and Ship Date using SUMIFS Worksheet function, Mixed Cell References and the EOMONTH Function. This is the classic Two Dates in a Fact Table Problem. MSPTDA #29 we solve it with Worksheet formulas and in MSPTDA #30 we use the Data Model and DAX formulas in both Excel Power Pivot and Power BI Desktop.
Topics:
1. (00:15) Introduction & look at Data Modeling Problem when we have two dates in a Fact Table.
2. (01:22) Side-By-Side Report for Total Sales for Order Date and Ship Date using SUMIFS and EOMONTH with Mixed Cell References using two conditions or criteria.
3. (04:22) Custom Number Formatting for both reports so we have useful labels in our report
4. (06:41) Cross Tabulated Report for Total Sales for Order Date and Ship Date using SUMIFS and EOMONTH with Mixed Cell References, and four conditions and criteria.
5. (09:37) Conclusion
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Рекомендации по теме
Комментарии
Автор

Hi Mike!
You are definitely one of the best teachers with your tutorials.

hamidbahari
Автор

And SUMIFS solution! It's such an easy and multi-functional function! Thanks for sharing! ♥

MalinaC
Автор

Many times SUMIFS is more useful than pivot tables. Thanks for sharing :) Cheers..

sachinrv
Автор

Besides fun with SUMIFS I also learned a custom formatting trick! Thanks for the informative video as always!

RobMichaels
Автор

Simple but very useful skill, especially to sales people who always have headache about order on hand the shipment

zhouyinwei
Автор

Proves to show that Excel Classic is still a method (and a force) to be reckoned with.
Nice clean solution where the array formulas where not that crazy at all.
Thanks, Mike.

GeertDelmulle
Автор

Hi Mike.. another great video. My initial go to solutions were:
in I5: =SUMPRODUCT($D$3:$D$20, --(YEAR(B$3:B$20)=YEAR($H5)))
in I15: =SUMPRODUCT($D$3:$D$20, --(YEAR($B$3:$B$20)=YEAR($H15)), --(YEAR($C$3:$C$20)=YEAR(I$14)))
You've mentioned that SUMIFS() exhibits better performance than an array calculation based formula and so is likely the better choice here. Never the less, fun to solve it in multiple ways. Thanks again for all the tips.. including the custom formatting. Thumbs up!

wayneedmondson
Автор

Package of 3 in 1 Video!!....Using different dates, sumifs and CUstom FOrmatting...Cool stuff Mike

mohitmanwani
Автор

Thank you for the fun SUMIFS solution! Looking forward to DAX :)

LeilaGharani
Автор

Mike, I learn something new every time, great video!

chrism
Автор

Another great class! Looking forward to seeing this using DAX!

jimfitch
Автор

Great explanation on the custom number formatting! Very informative!

shoeshines
Автор

Great Mike. Eager to see PP / DAX solutions.

enriquedominguez
Автор

Thank you so much Mike, great video as always, Have a great day Mike

katerina
Автор

Amazing Mike with another EXCELlent video. Thanks for the share.

SyedMuzammilMahasanShahi
Автор

Your videos are always amazing, Thanks you so much Mike.

ogwalfrancis
Автор

Thank you Mike... made my day again :)

edge
Автор

Great video Mike. Looking forward to the next episode. Please, when will you do a video on connecting to a folder with multiple excel files inside?
Thanks

EverythingDatawithHafeezJimoh
Автор

Hi, I see your sumif formulas for cross tabular is too long so I decide to use sumproduct funtion, my formula is here:
I15 =
Thanks for your video

hoangvo
Автор

You wouldn't like to go over the redistribution tirck used at the end? I can't quite figure it. 🤨🤨

williamarthur
visit shbcf.ru