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 RUclips. It's like the best brand out there ;)
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 🥳
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??? ..."
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.
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.
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!!!
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
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
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 :)
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.
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
Brand new to data models and power pivot tables. Still trying to figure out how to use a stacked line plot to display values from my source table (not sum or count). X vs. Y (i.e. date vs values). Excellent channel for excel help.
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.
Hello Leila. It's great to follow your videos. and could you please help me in excel that " I have a follow up each week and I need to lock last week data and open the next week data, so how can i lock previous week data and open next week data for timely updates." Thank you for your usual prompt response Leila
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.
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?
Not new. Been using the Power Tools for years now. I prefer to create a dCalendar table in PQ, so it’s fully automated - there are a lot of specific functions there that help with that.
Great video again. :) Why did you make a new helper column for sorting? I thought you will use the very first column with the complete dates for sorting. 🤔
1. what about custom fiscal years? 2. how to retain these settings for future use in other workbooks. it would be annoying to setup date hierarchy every time
Hi Leila. Great stuff. Trying to do something that seems simple but can't figure it out. Want to see the difference between two numbers (Say B2-c2) in two different columns and have that difference show up in a third column (say D2). Simple so far. But, I want a simple way to change the calculation for d2 to compare b2-e2 instead have have this done by changing the value of the cell at the top of the d column. So, I want d2 to calculate b2-c2, I put a "1" in D1, but if I put a "2" in d1, I want it to calculate b2-e2. Can this be done? I have multiple columns of numbers that I want to compare to one another and I want to be able to do this but simply changing the value of a fixed cell so I can compare the columns in various combinations. Confused yet? (Obviously, I am!). :)
Hi Leila, i love your clear and structured video "tutorials".. can you support me with feedback on the following.. I working on a large excel table in excel 365 (online version) with a few thousands rows and im tying to create a pivot where i can filter per year / book year..somehow group dates function doesn't appear.. any tips?
Thank you for this video! I've mirrored your steps including creating the new Date Table in the data model, and creating the relationship between dates from my range, and the date in the new Calendar sheet. However when I go back to excel and now add the Date field from the Calendar table, and the count of values from the Range (original data) table, the column where the date should go just displays as (blank), and the count just shows the count of ALL the data not grouped by date.... Anyone know where I'm going wrong?
Can't do that bc the actual date field automatically sorts alphanumerically vs numerically. Its the default setting used in pivot tables and is what causes the nuisance we saw in the very first demo. Ex. So because of the leading "1" digit, it means 12/8/2023 would come before 4/8/2023 but which isn't the case. As such, adding the formula twist converts those dates into purely numeric form which is what allows sorting based on numeric values, as opposed to alphanumeric. Comprenes?
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.
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.
great video as always, I am an advance user and would like to buy one of your xplus courses, the query and dax bundle, but I don´t find a coupon code anywhere, do you know where can i find one?, thx
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
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.
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." 😂
The calendar is not picking up the new year. How do I refresh to calendar so that I can use the new year in my pivot table selection when changing years?
My dates are just Month and Year. When I form a pivot table those months and years get converted into the full date format. So the dates Jul 2016, Aug 2016, Sep 2016, Oct 2016 etc, get converted by the pivot table to 1/07/2016, 1/08/2016, 1/9/2016, 1/10/2016 and so on. I don't know if that prevents this from working, but I can't get the dates to group into months and years, as you are doing in the pivot table. I am incredibly frustrated. Is there any way around this?
Hi, I have a couple of data on different dates. I want to put them in a chart, for eg how many orders were there in each week of every month. How to do that? To summarize the dates to 1st week, 2nd week etc..pls help
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....😉
Great tips, thank you Leila. One question - Is there a way to dynamically limit the dates to only show the date range in my data rather than the range in the calendar table I added to the data model? I used Power Query to look into a folder where these daily CSV files are stored and then I click refresh to update the report. Because I'm using the date from the calendar table, it shows every day for the entire year (through December 31, 2022) rather than just showing the date range I have data for. Thanks in advance!
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.
Brand new to the data model in Excel but this is a problem I have had analysing date ranges and love this clear explanation of how to work around it. Thanks {;>)x
Hi! In my excel (2021) I have dates but while grouping message flashes "cannot group that selection". I am unable to group my dates. I need to have month wise sales report. Any trick/advice or if I am missing something, my dates are in " date" format also
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 RUclips. It's like the best brand out there ;)
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 🥳
I had no idea the ctrl+z function removed the default grouping holy hell hahah. Thank you!!
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??? ..."
🤣
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!
One of the best videos I have seen about pivot tables
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
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.
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!
Thanks Leila. That sorting column within the model is a game changer.😀
Brand new to data models...been using excel for years, but I have learned so much from your videos.
Great to hear, Joanna!
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!!!
Brand new here! Going to take the dive and start getting comfortable with this
Brand new to Data Models, getting better with Pivot Tables--thanks for all the great content
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
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
DANG I’ve been so buggggged by this!!! You ROCK!!!
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.
brand new. this is JUST what I've been struggling with. THANK YOU!!!!!!!!
Happy to help!
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 :)
This was so helpful... You have no idea how easier my work has become lol
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.
❤❤❤❤❤❤❤❤❤❤ no words, only emotions!!!!!!!!!
THANK UYOUUUUUUUU!!!! OMG This dates issue has been such a thorn. THank you a ton
My pleasure :)
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 to data model. Thank you for sharing this information, Leila
Brand new and thanks for the clarification very helpful
How incredible this is exactly what I was looking for
Happy to help :)
Soooooo brand new 😃
Thanks so much Leila
Voice Of an Angel I'm just crushing on this woman here😍
Brand new to data models and power pivot tables.
Still trying to figure out how to use a stacked line plot to display values from my source table (not sum or count). X vs. Y (i.e. date vs values).
Excellent channel for excel help.
Brand new. It would help if you explained the formula a bit more (eg why +2? etc). Thanks for the vid.
That bugged me big time, it grouped when I did not need it, or I needed but had no idea how to group. Thank for the tips L!
You are so welcome!
Really tricky one. Thanks.
Brand new 😀 Thanks for this!
Really good stuff
Very usefull once again Leyla. I’m not usine data model very often, but this video will help!
Glad it was helpful, Claude!
Excellent video
Brand new but you're helping a great deal. Thank you
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.
Amazing video ... very useful
Very informative video❤
Brand New. Great tutorial!
Glad you liked it!
Another calculation of the quarter...
=ROUNDUP('Calendar'[Month Number]/3;0)
thanks for the informative video
Brand New to the data model.
Hello Leila. It's great to follow your videos. and could you please help me in excel that " I have a follow up each week and I need to lock last week data and open the next week data, so how can i lock previous week data and open next week data for timely updates."
Thank you for your usual prompt response Leila
brand new to datamodel
Excellent
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.
Thanks Leila
Thanks for this video! But wow, how unfortunate that Excel makes us go to so much work just to get dates formatted and sorted properly (:
u the best... thank you very much
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?
Not new. Been using the Power Tools for years now.
I prefer to create a dCalendar table in PQ, so it’s fully automated - there are a lot of specific functions there that help with that.
Group dates. Yeah I remember those.
Hi Leila
Great video as always, can you please make a video on how to use oracle crystal ball application
Thanks
I'm brand new to Data Table though I have been using pivot for years. Have always confused of how to group data. Hope I won't anymore 😅
Great video again. :) Why did you make a new helper column for sorting? I thought you will use the very first column with the complete dates for sorting. 🤔
1. what about custom fiscal years?
2. how to retain these settings for future use in other workbooks. it would be annoying to setup date hierarchy every time
Hi Leila. Great stuff. Trying to do something that seems simple but can't figure it out. Want to see the difference between two numbers (Say B2-c2) in two different columns and have that difference show up in a third column (say D2). Simple so far. But, I want a simple way to change the calculation for d2 to compare b2-e2 instead have have this done by changing the value of the cell at the top of the d column. So, I want d2 to calculate b2-c2, I put a "1" in D1, but if I put a "2" in d1, I want it to calculate b2-e2. Can this be done? I have multiple columns of numbers that I want to compare to one another and I want to be able to do this but simply changing the value of a fixed cell so I can compare the columns in various combinations. Confused yet? (Obviously, I am!). :)
Hi .. Kindly make a video for how to divide A sum of production in 30 days (Or in the month) in shuffling order.
Hi Leila
Hope, this message finds you well. Do you have any video on "How to became a MVP"? If yes, would appreciate if you please share the link.
I love you
My take away is that Amy really needs to get her sales numbers up. ;)
Hi Leila, i love your clear and structured video "tutorials".. can you support me with feedback on the following.. I working on a large excel table in excel 365 (online version) with a few thousands rows and im tying to create a pivot where i can filter per year / book year..somehow group dates function doesn't appear.. any tips?
Brand new to Data Model - always concerned it would bloat the spreadsheet size.
Thank you for this video! I've mirrored your steps including creating the new Date Table in the data model, and creating the relationship between dates from my range, and the date in the new Calendar sheet. However when I go back to excel and now add the Date field from the Calendar table, and the count of values from the Range (original data) table, the column where the date should go just displays as (blank), and the count just shows the count of ALL the data not grouped by date.... Anyone know where I'm going wrong?
brand new!
Why wouldn’t you use the actual date field when you want to sort by for the mm-yyyy column?
Can't do that bc the actual date field automatically sorts alphanumerically vs numerically. Its the default setting used in pivot tables and is what causes the nuisance we saw in the very first demo. Ex. So because of the leading "1" digit, it means 12/8/2023 would come before 4/8/2023 but which isn't the case. As such, adding the formula twist converts those dates into purely numeric form which is what allows sorting based on numeric values, as opposed to alphanumeric. Comprenes?
Maybe you could teach how to calculate year on year / month on month sales différence.
For more than 1 year it's impossible
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!
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
🤣
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.
great video as always, I am an advance user and would like to buy one of your xplus courses, the query and dax bundle, but I don´t find a coupon code anywhere, do you know where can i find one?, thx
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
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.
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." 😂
brand new!
👍
always struggled with this issue.... 😵💫😵💫
Thanks, Thanks, Thanks Liela
You’re most welcome 😊
The calendar is not picking up the new year. How do I refresh to calendar so that I can use the new year in my pivot table selection when changing years?
Brand new
How do I create date groups for fiscal periods? My company goes from the 29th to the 28th.
Could you please explain what if the value is not numbers?
My dates are just Month and Year. When I form a pivot table those months and years get converted into the full date format. So the dates Jul 2016, Aug 2016, Sep 2016, Oct 2016 etc, get converted by the pivot table to 1/07/2016, 1/08/2016, 1/9/2016, 1/10/2016 and so on. I don't know if that prevents this from working, but I can't get the dates to group into months and years, as you are doing in the pivot table. I am incredibly frustrated. Is there any way around this?
Very useful, which I struggle almost everymontg
I'm every day surprise by the powerful of Excel. Thanks a lot
Hi, I have a couple of data on different dates. I want to put them in a chart, for eg how many orders were there in each week of every month. How to do that? To summarize the dates to 1st week, 2nd week etc..pls help
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....😉
According to the SQLBI folks this function has been around since December 5, 2019. Makes me think it will never get added to Excel.
Hi Leila, what if my Q1 is not Jan-Mar. Instead, my Q1 is Apr-May?
Great tips, thank you Leila. One question - Is there a way to dynamically limit the dates to only show the date range in my data rather than the range in the calendar table I added to the data model? I used Power Query to look into a folder where these daily CSV files are stored and then I click refresh to update the report. Because I'm using the date from the calendar table, it shows every day for the entire year (through December 31, 2022) rather than just showing the date range I have data for. Thanks in advance!
If you use a value filter or the CALCULATE function to filter out the values,would it work?
Hi I have a problem it showing old dates which I don't need I tried filter etc but it's not working, how do I solve it?
I don't know where are you studying Excel... you are teaching something who does not know by anyone
Brand new to Excel data model, but have used relational data since MS Access 2.0
👍
DataFormat.Error: We couldn't parse the input provided as a Date value. how to handle this in get and transform?
And what if the dates don’t group automatically when I drag the dates field
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.
Brand new to the data model in Excel but this is a problem I have had analysing date ranges and love this clear explanation of how to work around it. Thanks {;>)x
Glad it was helpful!
Hi! In my excel (2021) I have dates but while grouping message flashes "cannot group that selection". I am unable to group my dates. I need to have month wise sales report. Any trick/advice or if I am missing something, my dates are in " date" format also
👌👌👌