Properly Handle Date Grouping and Missing Dates in Excel Pivot Tables (show all dates)
Вставка
- Опубліковано 13 лип 2024
- Get access to the full course here 👉 www.xelplus.com/course/excel-...
Working with dates in Excel pivot tables can get tricky. When you drag and drop a date field in the pivot table Excel automatically groups the dates into days, months and years. But with if you want to see the actual date and not the grouping? How can you quickly ungroup the dates? hint: There's an Excel shortcut to ungroup dates in a Pivot Table.
What if you'd also like to create different date grouping not available in the Pivot table? For example create a pivot table report showing fiscal periods or the weekday, quarter etc.
Another challenge you might come across is showing ALL the dates in the pivot table and not only the ones that have values. How can you unhide these dates with no values? It's really easy to setup. We'll be creating an automatic calendar table in the data model and then we can connect our tables together via relationships.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/pivottable-...
Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
00:00 How to Deal with Dates in Pivot Tables
00:33 Automatic Grouping & Ungrouping of Dates
02:05 Create Your Own Groupings
04:18 Use a Date Table in the Data Model
06:25 Create Relationship with Calendar Table
07:09 Create Power Pivot Table
08:07 How to Sort by Date in a Pivot Table
09:46 How to Display Dates Without Data
10:59 Show Data by Quarters in Pivot Table
12:32 How to Extend Calendar Table
13:06 Wrap Up
🎬 LINKS to related videos:
- Excel Productivity for Lazy (but Smart) People: • Excel Productivity for...
- How Power Query Will Change the Way You Use Excel:
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
Grab the file I used in the video from here 👉 pages.xelplus.com/pivottable-dates-file
I am an Engineer who actively works with excel. I cannot say how many times your videos save hours of searching.
If I need a single one stop solution, i just search for your videos only on UA-cam. It's like the best brand out there ;)
I had no idea the ctrl+z function removed the default grouping holy hell hahah. Thank you!!
I nominate this video as the most useful video this year. I've always knew about power pivot, just couldn't find time to look into it. This video opens my eyes to what is possible. It might even stop me using power query so much lol
Wow, thank you! I appreciate the kind words 🙏
Power Query and Power Pivot complement each other, spend some time learning DAX functions and writing measures to get the most out of Power Pivot. My workflow is usually PQ to obtain the Data, PP to shape it :)
More of this advanced stuff please! I always consider myself the Excel guru but your videos consider to show me stuff I never knew existed. Keep challenging us.
I grossly misunderstood "group dating" 😂. Jokes aside, this is very informative. I always struggled with dates in pivots.
That hadn't occurred to me until I saw the comments 🤣. At least the people who misunderstand can end up learning more about pivot tables 😉
My Chief got angry with me and this wtf word...
There are more problems non-Excel group dating.
I always am struggling with any issues abt Pivot!!! Still trying to work on it though
Mem please make a video on indian financial year date geouping , quarter, week number for indian FY. Please
Tricks to note:
1. To sort by column: year * 100 + month
2. To Create quarters: int( (month + 2) / 3)
Hello Miss
How to un pivot table?
Thank you. How about ISO WEEK?
Can try Timeline Slicer in the Pivot Table. Drop it in the Column or Row and it will create the time horizons (Month, Quarter, Year) for you.
What about days? I am trying to filter each day received and complete.
😂 i usually did int((mth-1)/3)+1, but this is better.
Such a clear explanation of calendar tables. I already knew a lot of the content, but was always intimidated by calendar tables. With my current project, not tackling it was making my work frustrating. Luckily I had saved this email. I'm getting ready to work the heck out of calendar tables! Thank you Leila!!!
Wow, I had to watch this twice because it is so great to be flexible to handle data without generating all again. Nicely explained Leila, Thanks!
This trick/technique is on the verge of being SICK!!!! I have fought this issue for almost 2 years in Power Pivot (before taking your class) and didn't realize the distinction of the Data Model - very embarrassing. I am so glad I have signed up for some of your classes. In addition to being extremely informative and educational, they are very enjoyable and entertaining. Some of the techniques you present are fantastic and are very thought provoking. You usually present so much information that I have found it much easier to watch each session completely and then re-watch working the problem (sample). Thanks
Yes Brand New, engineer here and your videos are helping me with project management.
This video helped me so much in creating a rolling 12 month sales / discount analysis. I am now able to sort by month/year (YYYYMM).
You are the best Leila!
I'm every day surprise by the powerful of Excel. Thanks a lot
DANG I’ve been so buggggged by this!!! You ROCK!!!
Brand new to the data model feature. I thought I was a power user but I always learn something new from you and direct any of my new consultants to your channel so they can brush up on their skills and learn something new too.
❤❤❤❤❤❤❤❤❤❤ no words, only emotions!!!!!!!!!
i was working with an excel file and facing issues with date grouping... then i was thinking to listen some song, this video automatically appeared on my home page.. now life is easy... thanks Leila.. you made my life easier...
Brand new here! Going to take the dive and start getting comfortable with this
Brand new to data models and its totally changing my life. Your videos are making this learning curve less daunting and more accessible. Thank you!
Great to hear!
Brand new to Data Models, getting better with Pivot Tables--thanks for all the great content
Thanks Leila. That sorting column within the model is a game changer.😀
Grateful to you for explaining how to sort months in YYYYMM format in Pivot tables. Big thankyou.
My students like to see either "Quarter 1" or "QTR 1" for a Quarter field. I use the DAX function FORMAT to do so, and the formula is ="QTR "&FORMAT([Date],"Q") to do this. "Q" returns the quarter number as a format string in DAX, which also works. FORMAT in DAX is much like the TEXT function in Excel, and uses the same format strings. TEXT does not recognize "Q" though, so in Excel, you would be forced to use the INT function, or ROUNDUP(MONTH([Date])/3,0) to get this. I found out about the "Q" string to return the quarter number working in Power BI. Power BI uses the same Data Model technology as in Excel, so the DAX is identical.
I share this, as FORMAT is easier to explain to users building a date table in Power BI. There is no easy button to build a date table there. LOL. You have to know all of the formulas to make one. Have a great day, Leila.
Leila making my life that little bit easier again! Thank you
Some say the perfect date is: dd-mm-yyyy.... Some say: yyyy-mm-dd. I personally would say Leila, an excel spreadsheet and a bottle of wine 🥳
Oh my god... blowing my mind - thank you so much! I have loved/hated these automatic PivotTable functions and now knowing how to work deeply with them is going to level up my skills immensely.
Great to hear!
exactly why i love this channel. Thanks so much!!
You're just too smart Leila...,
What a fantastic video. This is so helpful.
Wow Leila, hats off to your video! I am not "NEW" to Data Model, but just realized, how little I "KNEW" about using it effectively, especially for dates! I am sure, not only me, but many would benefit with these date manipulation techniques! Keep up the great work! 😊👍
Thank you, Vijay!
THANK UYOUUUUUUUU!!!! OMG This dates issue has been such a thorn. THank you a ton
My pleasure :)
One of the best videos I have seen about pivot tables
Brand new to the Data Model - thanks so much!
Absolutely mind-blowing. You are an Excel goddess! 🧡🧡🧡
New to DAX ... This video was extremely helpful. Thank you
Really clear explanations. Thank you so much. Great to use date table and know formulae for quarters and sort by and helper column. Great work!
Great to hear!
What can I say? Mindblown! I have worked soo many years and been strugling with the grouping of dates. Fantastic video! Thanks!!!
You're very welcome, Helena!
File ---- > Options---> Data----> Pivot tables disable automatic date grouping, automatic grouping of date/time columns in pivot tables,
We can activate or deactivate it from the options menu.
And
Leila's power pivot and dax lectures are incrediable....
Your knowledge about excel is like Galaxy of our Universe.❇❇
Multply years by 100 and add month number! So smart!
I love you and I wished I discovered you 20 years ago
Brand new to the data model but have been using pivot tables for years. Thank you so much for the work you do. Very helpful! 😀
Great to hear, Troy!
Your videos help me a lot to do my day-to-day work. thanks a million.
Brand new. Learn something new every time I watch your videos
That's great to hear!
always struggled with this issue.... 😵💫😵💫
Thanks, Thanks, Thanks Liela
You’re most welcome 😊
I've needed this video for so long. Thank you!
Brand new to data model. Thanks so much for your clear explanation!!!
Your are my teacher. When ever i am stuck i Excel i come back to your video for a solutions. Keep going all the best.
Glad to hear that!
Brand new to data model. Thank you for sharing this information, Leila
brand new. this is JUST what I've been struggling with. THANK YOU!!!!!!!!
Happy to help!
Great video explaining a complex subject
Brand New ,,all your contents are always amazing ,thx Leila👍
Yay! Thank you!
This is awesome! just what I needed.
Wow a lot of tips.
I was not aware of the Data Model option.
Just the abilty to change the formating of the data here was a huge revelation. Now the data created from my power queries have the correct formating.
Brand new. Thank you for the quick break down.
Great video as always. Thanks this is very useful for work.
Brand new ... but I still enjoy watching/listening to Prof Leila effortlessly skate from feature to feature ... as I try to figure out if anything mentioned relates to my work (which is why I will watch again... and again ... and again).
PS
At the 3:03 minute mark Leila says, " ... okay ... so far, so good...". Something I've said .... never. My response is usually, " oh gawddddd ... now WHAT happened? ... where did THAT number come from??? ..."
🤣
Brand new to data models...been using excel for years, but I have learned so much from your videos.
Great to hear, Joanna!
Wow, fantastic! I'l have to watch this a few more times to digest it all. Really great stuff. Thank you! I need to lean more about the data model side of things to take greater advantage of Excel.
Glad it was helpful, Jeff!
Thanks a million times. It was indeed helpful and would like to know more
Brand new and thanks for the clarification very helpful
Brand new to DAX. Enjoying your course
Brand new and love your content.
Excellent! Very helpful!
Much appreciated!👍
Great video an explanation! Thanks so much! Love your videos!
Thanks for watching!
Thank you for the nicely delivered content and helping us learn new tools
Soooooo brand new 😃
Thanks so much Leila
Brand new to the data model and so much to learn!
How incredible this is exactly what I was looking for
Happy to help :)
Very useful, which I struggle almost everymontg
Thanks for this Xelplus!
My pleasure!
Brand new
I will find many uses for this soon. Thank you
This resolved the exact issue I just had, excellent!
Queen of Excel 👸
I never understood the importance of power pivot
Your videos are so easy to follow and informative
Secret heart of pivot table ...thanks liela
Thanks so much for presenting in a simple and easy to follow method. Am trying to enhance my knowledge everyday and thanks to you i am slowly achieving that
Thank you for the kind feedback!
Brand new 😀 Thanks for this!
This is exactly what I was looking for!
Brand New - Great Information
Leila, you are 100% my first port of call when I'm exploring Excel capabilities!!
Your video is so clear and super easy to follow - Thank you so much for sharing your knowledge with the world, you've saved me hours!!
One question I do have, and apologies you've covered it in another video - is there any way to show 12 monthly rolling periods for sales in a pivot table? and how would you go about this?
Any help would be fantastic
Thanks for video! Very good
Brand new but you're helping a great deal. Thank you
Amazing video ... very useful
Wow!! This is amazing
Excellent!! 👏👏👏
Brand new to the data model! I love being able to count distinct using it now.
👍
Excellent video
Very helpful, thank you
Your are amazing !! Thanks a lot
Really tricky one. Thanks.
Thank you SO MUCH.
Really good stuff
Excellent video thanks Leila!
Thanks for stopping by, Chris!
I saw the title and assumed it was about group dating (like going on a double date) and pivot tables were a new thing in restaurants. Took me two seconds to process that it’s about excel.
you are funny
🤣
Brand new to data model, struggles with dates grouping. Thank you so much.
You got this 🙌
I've never seen or used the data model. This is great info.
👍
Brand new. Amazing.
Very informative video❤
Brand New. Great tutorial!
Glad you liked it!
thanks for the informative video
Thanks for the video, Im Brand new to data models
Very usefull once again Leyla. I’m not usine data model very often, but this video will help!
Glad it was helpful, Claude!