Im a serial commentor on this Excel series, but a huge, huge thank you. I've watched a number of Data model excel tutorials, but this was by far the most comprehensive. Also, that serial shortcut trick was outstanding.
I have not done deeper advanced DAX. I am sorry. I will try to learn more and make videos in the future. What real business problems are you interested in?
Thanks for the reply, Mike. Multiple fact tables, Many to many, ragged hierarchies, different granularity of data e.g. budget and actuals, ABC classification, Basket analysis, banding (age buckets), running total, moving average total, top n products etc. come to my mind when I think of real business problems and I've had to deal with them big time. However, DAX provides an opportunity to solve these problems even when data runs in millions of rows, therefore, it's a big thing for businesses too.
Amitabh Dutta , Well thank you for watching and enjoying. You can always help when you watch by clicking Thumbs Up and leaving a comment : ) Then you will support the excelsifun here at RUclips : )
Thanks a million Mike my great teacher, i am looking forward to your next video, I can't wait to hear you and learn something new. you do something incredible.
Yeah, fantastic video Mike. I love Excel 2016 but need more RAM to keep up. I can now also model what a 10% change in the UK markets will do really easily - BRRRREXIT!
Totally agreed. That was a major improvement in 2016 power pivot specially that now you don't have to sort the month which was a drag in the past. Now, I upload my data model, and use the calendar feature and if I need to add quarters, I would use the rank function divided by 3 . One last thing: If you add more data in your data model in order to refresh your calendar table you need to go calendar date table and click update range and select your new range. Thanks for sharing all your knowledge the and your great tips. Regards Luis J Azuaje
Thanks to you too! It is great to be an an awesome Online Excel team with you and many others! I always depend on learning new things from you and all of the other Online Excel Teammates.
Hi Micheal, Firstly thanks for your great effort, Secondly all the the download files are not allowed due to some forbidden reasons so can please find out why,I'm talking about highline excel lessons class 22, thank you again and thumbs up as usual.
Hey Mike, many of the functions you are using are not availeable in the Offce 365 Business plan (the Power Pivot ribbon is missing entirely, and with that commands such as Manage Data Model). After some fumbling and googling I figured out that I need to upgrade to the Office 365 ProPlus plan. In order to do so, I had to go to the Office 365 Admin Center (you can't upgrade from within your regular user account). The ProPlus plan is 50% more expensive than the regular Business plan (pricing might vary depending on your region). Just sharing my experiences with other users who might face similar issues. Now I am back on track, super excited to continue with your course!
Thanks.I was looking forward to this video. Mike: what is the advantage/disadvantage on using the built in feature in 2016 in PowerPivot that allows you to create a calendar table within the data model instead of doing it manually? Cheers
I think you mean "What are the advantage/disadvantage for built-in Group by Date feature for the Data Model that adds Calculated Columns to the Fact Table"? Right? You asked about creating an automatic Calendar table. I do not know of an automatic method for creating a Calendar Table? Do You? Am I missing something? I demonstrated the built-in Group by Date feature for the Data Model that adds Calculated Columns to the Fact Table" 11: 38 minute in Video #17. I also answered the question "What are the advantage/disadvantage for built-in Group by Date feature for the Data Model in this video in great detail. I also have written notes that answer your question about the auto Grouping Feature in Excel 2016 Data Model in the downloadable pdf notes. To answer your question here: 1) According to Microsoft, for small data sets (a few million records), the Automatic Grouping of Dates in a Fact Table works just fine. 2) The automatic Grouping Feature only does "Standard Quarters". So if you need Fiscal Quarters, you must use a Calendar Table. 3) The DAX Time Intelligence Functions like SAMEPERIODLASTYEAR do not work with the automatic Grouping Feature. 4) The Columnar Database and DAX formulas are designed to work best with dimension tables (Calendar Table is a dimension table) and Fact Tables. For Big Data, the grouping feature may slow things down. 5) The Grouping by Data in 2016 Data Model is very new and not many people use it yet, but for small data sets where you just need month and year, it is a really amazing feature!!
If you go inside powerpivot under design , calendars ,datetable and hit new, it will create a list of days with years , month number, month , even a YYYY-MM , dateoftheweek number ,dayofweek name etc, that are bound to the data model and it does sort the months ( Jan, feb ,mar) automatically. You don't need to create the date tables in excel : You can do it directly in powerpivot . I have been using this feature since the 2016 version came out which was a great improvement from the 2013 version. Thanks for the great tricks I have learned from your videos.
That is amazing!!!!! I knew about the Group by Date to create Date Calculated Columns in the Fact Table, but I did not know about the auto Calendar Table! I am going to go and try it now! Thanks for the awesome tip!!!!!
That is so amazing!!! I did not know about this, but it is so easy and amazing!!! Thanks for being a great Online Excel Teammate and helping me to learn more great things about Excel!
This is super helpful, thank you! I do have a question though - how can I calculate the %change week over week? I found functions for year, quarter, and month that might help with MoM or QoQ calculations etc. but hit a dead end with WoW change.
Hey, thanks a lot for this. I ran into a weird problem using Excel 2013. The Gross Profit % Change Measure appears correctly in the Pivot Table except at the total row, where it gives a total of -0.00 % instead of 26.54 %! In the data model view, the DAX measure also reads: "Gross Profit % Change: 0.00 %" I did everything exactly like you did, except I'm in Excel 2013. And the measure is showing the correct results for each row. Only the total is messed up.
OK I actually did NOT do everything exactly like you. I made the dCalendar Table bigger, up to 2025. So obviously that messed it up, because when I shrunk it back to the end of 2018 everything worked perfectly. But then do we really need to have to expand our calendar tables manually each time we have new data? Isn't there a better approach?
Hello Sir, Why to use DAX Measures when already Pivot Table has In-built aggregate functions? Just to add columns in Datamodel and use Pivot table aggregate functions. It will be easier. I can manually change the number formatting in Pivot table.
Hi I have a complex worksheet that someone else created at work but they have now left, can you help me with it, how much would you charge for this? It's a certain formula I don't understand, can you help?
Sometimes, I wonder how can a man be so explicit in the way to detail. If it were not for you, I would have kept myself aloof from learning excel. Thanks a zillion. By the way, I got a question. This is not related to what you have taught here, though. It is about extracting unique data from a list of data with numbers, blanks, and text. I have already got it sorted partially using =IFERROR(1/(COUNTIF(E$3:E3,E3)=1),"") whereas the rows start from E3 resulting 1 for unique values. Now I would like to know if you care, the easiest method to extract the unique list other than using frequency function. Sorry to have bothered. Good day.
You should use FREQUENCY and NEVER use IFERROR. Why? because as I demonstrate in my book Ctrl + Shift + Enter and in the video series about the book (ruclips.net/p/PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci) : 1) FREQUENCY can be many times faster than using COUNTIFS and 2) It is better to choose an alternative logical test and use IF rather than use IFERROR because IFERROR can be really slow. If you go to playlist video #16 discusses IFERROR problem and video 17 discusses FREQUENCY vs. COUNTIFS and video #23 discusses unique lost formulas. Really, though, we should use Power Query to get a unique list: ruclips.net/video/3ICk356kEZo/видео.html or maybe ruclips.net/video/ZAg7AzBkg30/видео.html
Mr. Girvin, your channel is soooo much better than Netflix!
Im a serial commentor on this Excel series, but a huge, huge thank you. I've watched a number of Data model excel tutorials, but this was by far the most comprehensive.
Also, that serial shortcut trick was outstanding.
It's Sunday afternoon in April 2020, I'm in the UK, locked in my home due to a COVID-19 pandemic - and this tutorial is AMAZING! Thank you!
You are by far the best Excel teacher I know. Your explanations are superb!
Thank you very much for the kind words! Coming from a great Excel Teacher like you, it is really nice! Go Online Excel Team!!!!
It is too great, it's an amazing job that you just made. sincerely you are a great teacher who deserves all the respect and encouragement.
Glad you like it! To give thanks just click Thumbs Up!
WoW, we're even considering diff between RAM and CPU when using excel now!! How advanced it is!!
Big Data : )
Awesome video. It doesn't get clearer than this. Waiting for deeper and advanced DAX sessions solving real business problems!!!
I have not done deeper advanced DAX. I am sorry. I will try to learn more and make videos in the future. What real business problems are you interested in?
Thanks for the reply, Mike. Multiple fact tables, Many to many, ragged hierarchies, different granularity of data e.g. budget and actuals, ABC classification, Basket analysis, banding (age buckets), running total, moving average total, top n products etc. come to my mind when I think of real business problems and I've had to deal with them big time. However, DAX provides an opportunity to solve these problems even when data runs in millions of rows, therefore, it's a big thing for businesses too.
Better than most Udemy courses. Great stuff!
Thanks for the kind words! But here at the excelisfun channel it is not free, you have to leave a comment and thumbs up on each video as pay ; )
I can call you my excel Guru.....utmost respect from India......In india, a Guru is beyond God
You are welcome, Amitabh!!!! Thank you for the support with your Thumbs Up, Comment and Sub : )
wow.......you replied......to me....OMGGGGGGGGGGGGGGGGGGGG...cant believe....you are a celebrity and i m a huge fan of your tutorials........
Amitabh Dutta , Well thank you for watching and enjoying. You can always help when you watch by clicking Thumbs Up and leaving a comment : ) Then you will support the excelsifun here at RUclips : )
Great lecturer,
I really appreciate you for doing all this for free ONLINE.
Remain Blessed.
To bless: just click Thumbs Up! Glad you like the video!
Done already
Thanks!
More videos soon : )
Thank you!!!
I have learned a lot of things from you! not only knowledge in excel but the way how you explain. Amazing!
You are welcome! Thanks for the Thumbs Up on each vid and Sub : )
Thanks to care for giving such a soothing solution. Long live sir!
You are welcome! Thanks for clicking Thumbs Up on all the videos!
Amazing explanation 😊👌
Glad you like it : )
Thanks a million Mike my great teacher, i am looking forward to your next video, I can't wait to hear you and learn something new. you do something incredible.
You are welcome a million! Thanks for always clicking the Thumbs Up!
Awesome tutorial! You're the best in Excel!!!! Thanks a lot!
You are welcome, Armond!!!! Thanks for your support : )
Secret dropdown - awesome Mike!!
Really amazing , Professor . Much much better than I expected . Thanks , best regards
Thank you for your sharing. This is great VDO.
You are welcome, Wunlapar! Thank you for the support with your comment, thumbs up and your Sub : )
Yeah, fantastic video Mike. I love Excel 2016 but need more RAM to keep up.
I can now also model what a 10% change in the UK markets will do really easily - BRRRREXIT!
Glad you like the video! Yes, RAM does matter for big data...
Thanks a lot Mike
Great video from great teacher
You are welcome!
Great video!
Row context is just like implicit intersection :)
Thanks, pmsocho!!!
Yes. Another Great Video ... Thanks Mike.
Thanks for your support!
Amazing video!!
Start with fun. Good morning from Korea.
Good moring in Korea!!!! This is a good one : )
Completed with fun. Thank you very much sir.
Amazing,it's the most useful tips, thanks
Totally agreed. That was a major improvement in 2016 power pivot specially that now you don't have to sort the month which was a drag in the past. Now, I upload my data model, and use the calendar feature and if I need to add quarters, I would use the rank function divided by 3 . One last thing: If you add more data in your data model in order to refresh your calendar table you need to go calendar date table and click update range and select your new range. Thanks for sharing all your knowledge the and your great tips. Regards Luis J Azuaje
Thanks to you too! It is great to be an an awesome Online Excel team with you and many others! I always depend on learning new things from you and all of the other Online Excel Teammates.
I added a note about the Automatic Calendar Table at the 14:45 minute mark to our Excel Teammates can learn about your great tip!
EPIC video! Thanks Mike.
Glad you like it!!!
Amazing video! thanks
Glad you like it!
Thx 4 the DAX Formulas!!!
You are welcome, Rad Tim!!!!
TruthofDilly, I am sorry but an not available for consulting at this time. Try mrexcel.com
Great video Mike. I defiantly need to upgrade to 2016.
Glad you like it! The Data Model and DAX Formulas are the best yet, in Excel 2016!
Hi Micheal, Firstly thanks for your great effort, Secondly all the the download files are not allowed due to some forbidden reasons so can please find out why,I'm talking about highline excel lessons class 22, thank you again and thumbs up as usual.
Hey Mike, many of the functions you are using are not availeable in the Offce 365 Business plan (the Power Pivot ribbon is missing entirely, and with that commands such as Manage Data Model). After some fumbling and googling I figured out that I need to upgrade to the Office 365 ProPlus plan. In order to do so, I had to go to the Office 365 Admin Center (you can't upgrade from within your regular user account). The ProPlus plan is 50% more expensive than the regular Business plan (pricing might vary depending on your region). Just sharing my experiences with other users who might face similar issues. Now I am back on track, super excited to continue with your course!
Hi when I add data through excel I am getting double values
Thanks.I was looking forward to this video. Mike: what is the advantage/disadvantage on using the built in feature in 2016 in PowerPivot that allows you to create a calendar table within the data model instead of doing it manually? Cheers
I think you mean "What are the advantage/disadvantage for built-in Group by Date feature for the Data Model that adds Calculated Columns to the Fact Table"? Right? You asked about creating an automatic Calendar table. I do not know of an automatic method for creating a Calendar Table? Do You? Am I missing something?
I demonstrated the built-in Group by Date feature for the Data Model that adds Calculated Columns to the Fact Table" 11: 38 minute in Video #17. I also answered the question "What are the advantage/disadvantage for built-in Group by Date feature for the Data Model in this video in great detail. I also have written notes that answer your question about the auto Grouping Feature in Excel 2016 Data Model in the downloadable pdf notes.
To answer your question here: 1) According to Microsoft, for small data sets (a few million records), the Automatic Grouping of Dates in a Fact Table works just fine. 2) The automatic Grouping Feature only does "Standard Quarters". So if you need Fiscal Quarters, you must use a Calendar Table. 3) The DAX Time Intelligence Functions like SAMEPERIODLASTYEAR do not work with the automatic Grouping Feature. 4) The Columnar Database and DAX formulas are designed to work best with dimension tables (Calendar Table is a dimension table) and Fact Tables. For Big Data, the grouping feature may slow things down. 5) The Grouping by Data in 2016 Data Model is very new and not many people use it yet, but for small data sets where you just need month and year, it is a really amazing feature!!
If you go inside powerpivot under design , calendars ,datetable and hit new, it will create a list of days with years , month number, month , even a YYYY-MM , dateoftheweek number ,dayofweek name etc, that are bound to the data model and it does sort the months ( Jan, feb ,mar) automatically. You don't need to create the date tables in excel : You can do it directly in powerpivot . I have been using this feature since the 2016 version came out which was a great improvement from the 2013 version. Thanks for the great tricks I have learned from your videos.
That is amazing!!!!! I knew about the Group by Date to create Date Calculated Columns in the Fact Table, but I did not know about the auto Calendar Table! I am going to go and try it now! Thanks for the awesome tip!!!!!
That is so amazing!!! I did not know about this, but it is so easy and amazing!!! Thanks for being a great Online Excel Teammate and helping me to learn more great things about Excel!
Love the vids!
Glad you like them!
This is super helpful, thank you! I do have a question though - how can I calculate the %change week over week? I found functions for year, quarter, and month that might help with MoM or QoQ calculations etc. but hit a dead end with WoW change.
sometime while importing data get a binary error in power query , can please explain how to resolve the same .
Many Thanks
I do not know!?!?! Maybe you didn't use the Excel.Workbook function!?!? Anyone else know?
I have also such kind of problem. It's saying your configuration doesn't meet the requirements even it was not huge data. Can you give any advice ?
Thanks
You are welcome, WRH!!! : )
Great Video once again Mike, just one question, how do you calculate Fiscal quarter column for non US dates? i.e for Australia in my case
Hey, thanks a lot for this. I ran into a weird problem using Excel 2013. The Gross Profit % Change Measure appears correctly in the Pivot Table except at the total row, where it gives a total of -0.00 % instead of 26.54 %! In the data model view, the DAX measure also reads: "Gross Profit % Change: 0.00 %"
I did everything exactly like you did, except I'm in Excel 2013. And the measure is showing the correct results for each row. Only the total is messed up.
OK I actually did NOT do everything exactly like you. I made the dCalendar Table bigger, up to 2025. So obviously that messed it up, because when I shrunk it back to the end of 2018 everything worked perfectly. But then do we really need to have to expand our calendar tables manually each time we have new data? Isn't there a better approach?
Hello Sir, Why to use DAX Measures when already Pivot Table has In-built aggregate functions?
Just to add columns in Datamodel and use Pivot table aggregate functions. It will be easier.
I can manually change the number formatting in Pivot table.
Hi I have a complex worksheet that someone else created at work but they have now left, can you help me with it, how much would you charge for this? It's a certain formula I don't understand, can you help?
I have also such kind of problem. It's saying your configuration doesn't meet the requirements even it was not huge data. Can you give any advice ?
I do not know what the configuration error means. Does anyone else know what it means?
Sometimes, I wonder how can a man be so explicit in the way to detail. If it were not for you, I would have kept myself aloof from learning excel. Thanks a zillion. By the way, I got a question. This is not related to what you have taught here, though. It is about extracting unique data from a list of data with numbers, blanks, and text. I have already got it sorted partially using =IFERROR(1/(COUNTIF(E$3:E3,E3)=1),"") whereas the rows start from E3 resulting 1 for unique values. Now I would like to know if you care, the easiest method to extract the unique list other than using frequency function. Sorry to have bothered. Good day.
You should use FREQUENCY and NEVER use IFERROR. Why? because as I demonstrate in my book Ctrl + Shift + Enter and in the video series about the book (ruclips.net/p/PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci) : 1) FREQUENCY can be many times faster than using COUNTIFS and 2) It is better to choose an alternative logical test and use IF rather than use IFERROR because IFERROR can be really slow. If you go to playlist video #16 discusses IFERROR problem and video 17 discusses FREQUENCY vs. COUNTIFS and video #23 discusses unique lost formulas.
Really, though, we should use Power Query to get a unique list: ruclips.net/video/3ICk356kEZo/видео.html or maybe ruclips.net/video/ZAg7AzBkg30/видео.html
OMG