Properly Handle Date Grouping and Missing Dates in Excel Pivot Tables (show all dates)

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/pivottable-dates-file

[@ashutoshaurum]

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 KZbin. It's like the best brand out there ;)

[@ArticulateDesignASMR]

I had no idea the ctrl+z function removed the default grouping holy hell hahah. Thank you!!

[@YasirBai]

Tricks to note: 1. To sort by column: year * 100 + month 2. To Create quarters: int( (month + 2) / 3)

[@RichVideo5493]

Hello Miss How to un pivot table?

[@ledward95]

Thank you. How about ISO WEEK?

[@ronaldtsang4272]

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.

[@hassan06ny]

What about days? I am trying to filter each day received and complete.

[@pavelbursa9247]

😂 i usually did int((mth-1)/3)+1, but this is better.

[@RichardJones73]

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

[@LeilaGharani]

Wow, thank you! I appreciate the kind words 🙏

[@stevemorris4938]

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 :)

[@Humphrey181]

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.

[@babiecacorriente762]

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...

[@ashokwwf]

I grossly misunderstood "group dating" 😂. Jokes aside, this is very informative. I always struggled with dates in pivots.

[@LeilaGharani]

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 😉

[@lll-xo6nk]

My Chief got angry with me and this wtf word...

[@daveg5857]

There are more problems non-Excel group dating.

[@ShowbeYPainT]

I always am struggling with any issues abt Pivot!!! Still trying to work on it though

[@creative_gujarati]

Mem please make a video on indian financial year date geouping , quarter, week number for indian FY. Please

[@ruudb72]

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!

[@kaypaboojian9922]

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

[@kwanklan8652]

Yes Brand New, engineer here and your videos are helping me with project management.

[@teoxengineer]

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....

[@cheryldavis9797]

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!!!

[@PGOManc7]

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 🥳

[@chh8860]

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??? ..."

[@LeilaGharani]

🤣

[@IAMGregEVA]

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.

[@LeilaGharani]

Great to hear!

[@jtmh31]

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.

[@lokinindichandanprasad4824]

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.

[@LeilaGharani]

Glad to hear that!

[@DeaconWoodworks]

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.

[@dlep9221]

I'm every day surprise by the powerful of Excel. Thanks a lot

[@helenasvensson6595]

What can I say? Mindblown! I have worked soo many years and been strugling with the grouping of dates. Fantastic video! Thanks!!!

[@LeilaGharani]

You're very welcome, Helena!

[@vijayarjunwadkar]

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! 😊👍

[@LeilaGharani]

Thank you, Vijay!

[@tiannacorder2220]

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!

[@LeilaGharani]

Great to hear!

[@jbellehumeur8010]

Multply years by 100 and add month number! So smart! I love you and I wished I discovered you 20 years ago

[@rickfini6515]

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!

[@surajbhardwaj428]

Your knowledge about excel is like Galaxy of our Universe.❇❇

[@peeyush7631]

Grateful to you for explaining how to sort months in YYYYMM format in Pivot tables. Big thankyou.

[@Doraemon-ed3js]

Not even ChatGPT was able to provide the answers I found in this video. THANK YOU SO MUCH! You are awesome!

[@eleanorkeddy9671]

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

[@gwenmueller3473]

Brand new to Data Models, getting better with Pivot Tables--thanks for all the great content

[@shahshrey108]

One of the best videos I have seen about pivot tables

[@DamonAustinJ]

Brand new. Learn something new every time I watch your videos

[@LeilaGharani]

That's great to hear!

[@alparkson5494]

Thanks Leila. That sorting column within the model is a game changer.😀

[@joannacook6048]

Brand new to data models...been using excel for years, but I have learned so much from your videos.

[@LeilaGharani]

Great to hear, Joanna!

[@anillpatel]

You're just too smart Leila..., What a fantastic video. This is so helpful.

[@rusektor]

08:07 There's also one trick. You can create AutoFilter on ALL PivotTable columns (in this case - A3:B3). In order to do that you need: 1) select C3 cell and 2) apply AutoFilter (Data -> Filter). Now you will see dropdowns on ALL columns.

[@victoriagleason377]

New to DAX ... This video was extremely helpful. Thank you

[@ianstaines]

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.

[@NikitaSharma-bs4gg]

I never understood the importance of power pivot Your videos are so easy to follow and informative

[@freezendog7]

People might not realise, but this is a power video with a lot more tricks than 1st meet the eyes. Well done Leila, I have that power query course of yours, just can't seem to find the time to get through it at the moment. All the best.

[@LeilaGharani]

Thank you! 😃

[@ShikhorKhan]

Your videos help me a lot to do my day-to-day work. thanks a million.

[@jhnc6960]

Brand new here! Going to take the dive and start getting comfortable with this

[@MirageSolange]

Voice Of an Angel I'm just crushing on this woman here😍

[@VeretenoVids]

Brand new to data model! Total newbie here, though I've been playing with pivot tables for years--mostly because I'm largely self taught and nobody told me pivot tables are supposed to be "hard." 😂

[@KavyaJoshy]

The Best learning. Also the best part is you provide the worksheet download option so that we can practise

[@jeffcecchini3588]

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.

[@LeilaGharani]

Glad it was helpful, Jeff!

[@robertc7323]

Wow, this is creepy. I have just been trying (unsuccessfully) to figure out how to do this with pivot tables last week for work, and now you've made a video on it! [Brand New]

[@LeilaGharani]

Glad the timing worked out 😁

[@raasheedpakwashi2961]

exactly why i love this channel. Thanks so much!!

[@lucianoriquet8552]

Brand new to DAX. Enjoying your course

[@alanward9806]

brand new. this is JUST what I've been struggling with. THANK YOU!!!!!!!!

[@LeilaGharani]

Happy to help!

[@venkatiyer7459]

Hi! Mam ... I am brand new for data models .. I am stunned to explore the possibilities .... Thanks a lot to you as usual... God Bless You

[@sidhonor675]

Thanks

[@paulwillyams4871]

Great video explaining a complex subject

[@ldmlhome]

DANG I’ve been so buggggged by this!!! You ROCK!!!

[@Buhassan5656]

always struggled with this issue.... 😵‍💫😵‍💫 Thanks, Thanks, Thanks Liela

[@LeilaGharani]

You’re most welcome 😊

[@sireynolds]

This is an awesome video. Did not realise you could do that with dates in a pivot table. I have spent ages previously putting extra columns in my table to do the same thing. TY!

[@LeilaGharani]

Glad it was helpful!

[@chandrujayasankaran814]

Thank you for the nicely delivered content and helping us learn new tools

[@emare4117]

Absolutely mind-blowing. You are an Excel goddess! 🧡🧡🧡

[@troypeterson9949]

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! 😀

[@LeilaGharani]

Great to hear, Troy!

[@almenyawe]

Brand New ,,all your contents are always amazing ,thx Leila👍

[@LeilaGharani]

Yay! Thank you!

[@barttitulaerexcelbart9400]

Very good Leila! you might want to add a remark later on this video: In Power BI there is a new function =QUARTER(date), I think/hope this function will also be available in Excel/PowerPivot someday....In Power BI you also have a new FORMAT function: FORMAT (date;"mmm",[LocaleName]). there you can choose to display the month in any language. But we have to wait for this....😉

[@brianxyz]

According to the SQLBI folks this function has been around since December 5, 2019. Makes me think it will never get added to Excel.

[@newmandoss]

Very useful, which I struggle almost everymontg

[@hebertschweitzerracing]

Brand new to the data model! I love being able to count distinct using it now.

[@LeilaGharani]

👍

[@MrPotatoeHead002]

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

[@LeilaGharani]

Thank you for the kind feedback!

[@MichaelCradock-x5p]

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!

[@LeilaGharani]

Great to hear!

[@pravinr197]

Brand new. I was taught that integer and floating points are data type and different variables while I studied in school. Now I came to know that int can be used as a function in excel.

[@awallaustin]

THANK UYOUUUUUUUU!!!! OMG This dates issue has been such a thorn. THank you a ton

[@LeilaGharani]

My pleasure :)

[@nadineloof8906]

Brand new to the data model and so much to learn!

[@aperkinsvt]

Thanks for another great video! Question: is there a way to set up a custom date table with all the groupings I use and then reference / import that table into any new workbooks - so I don’t have to create a new calendar table each time?

[@adriannachumpuka7213]

This was so helpful... You have no idea how easier my work has become lol

[@sandywong5074]

Brand new to data model. Thanks so much for your clear explanation!!!

[@hinasarwath8377]

Brand new to data model. Thank you for sharing this information, Leila

[@pixelmasque]

when you open the data model, is excel working from a copy of the date table and the salesTable in the data model ( a copy of the original salesTable in the sheet)? or is power query directly referencing the sheet table for sales? Because my salestable will be constantly edited and added to by multiple users...(edit) thats cool I can see in the pivot pane the tablesales is being referenced directly as the source for the pivot table,... nice.

[@bewilderbeast]

Thanks!

[@LeilaGharani]

Thank you!

[@saxvine3696]

Thanks a million times. It was indeed helpful and would like to know more

[@sat1460]

Secret heart of pivot table ...thanks liela

[@nollaf126]

I've never seen or used the data model. This is great info.

[@LeilaGharani]

👍

[@ranjanishamasundar6855]

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.

[@sethmaundu3421]

you are funny

[@LeilaGharani]

🤣

[@Power2InspireUK]

Leila, great videos. I have 4 years with little data and then 3 with plenty of data. How do I amalgamate the first four years and keep 2022 -2024 as separate years?

[@philipherlihy3119]

Brand new to the Data Model - thanks so much!

[@khanyasser]

How incredible this is exactly what I was looking for

[@LeilaGharani]

Happy to help :)

[@AmitGupta-bl2iu]

Best explanation l am from india🇮🇳

[@KennyMcM1983]

Great video as always. Is there a way to change the date format on a pivot table from US to UK format when the report is download is in US format? Tried to do it using custom but wouldn't work.

[@1701Ender]

I've needed this video for so long. Thank you!

[@michaelanibom5135]

Soooooo brand new 😃 Thanks so much Leila

[@Lyriks_]

This new hair color looks very nice ! Oh and great video btw

[@LeilaGharani]

Thank you!!

[@nwjp19]

Brand new to data model, struggles with dates grouping. Thank you so much.

[@LeilaGharani]

You got this 🙌

[@jayc3431]

at 1:11 when I drag Order Date to rows, I'm not seeing Month's (order date). Is there a reason its not showing up for me?

[@sanjuburkule]

Use 'Text to Columns' and change format of Order Date to 'Date'. Then it works.

[@ClaudeBalleux]

Very usefull once again Leyla. I’m not usine data model very often, but this video will help!

[@LeilaGharani]

Glad it was helpful, Claude!

[@adriansegura5229]

This is exactly what I was looking for!

[@suvzar6194]

Can't tell you how useful this video is. I have an additional question. In my data i don't have direct monthly sales data. My sales person sold item z 100 qty on Monday at 10 rs. He sold same item 150 qty on Tuesday at 11 rs. Then sold same item 90 qty wednesday at 9.5 rs. Now I want to plot a pivot graph of how the average price per month has fluctuated. I have been doing this manually to shewat extent since long and your support will be highly appreciated.

[@titan2793]

Brand new. Thank you for the quick break down.

[@chisamuel7168]

Brand new and thanks for the clarification very helpful

[@miguel.sanches]

Great video an explanation! Thanks so much! Love your videos!

[@LeilaGharani]

Thanks for watching!

[@rongxuantan5406]

I'm trying to group my dates by billing cycle, e.g. 12 may - 12 June is one month. Not sure how to do that initially as pivot tables group months by 1st to 30th/31st, but now it seems like I can add another calendar table with my desired grouping and establish a relationship. Will try that and see how it goes

[@jamjam3188]

Hi Leila, is there any chance of showing how to change Q1 starts from April please.

[@GaryKlineCA]

Did not know Excel had that data model feature. That really opens a lot of possibility, cool!

[@LeilaGharani]

It really is a new dimension!

[@Holder3317]

do you have a video on how to make a slicer to switch pivot chart, with say monthly data for 5 years, from month, quarterly and yearly reporting?

[@TheJoshtheboss]

I have seen some other videos using Power Query instead to create a custom Calendar Table. Is there any preference or benefit of one method over the other? Thanks.

[@ismailshehadeh3889]

Queen of Excel 👸