Thank you for sharing your knowledge and simple files Thank you very much... because from the video you can follow it step by step easily using example files
Thanks for those kind words Monica. I sure can do one. It wasn't planned but as I am currently recording a "Time Intelligence in Excel and Power BI Data Models" Video Course I could post a "YTD snippet" to UA-cam.
Great video. Can't help but think there is a simpler way, but I'm sure you tried to find something easier. 2 things - 1) in some countries/organizations, the fiscal year is referred to by the ending year (so the 2020-2021 year is called 2021), how would you change your powerquery? 2) how would you change this for a different starting month? would be nice to have a parameter so that it could be changed dynamically.
Hi Ron. 1. That is exactly what I did and that made the whole thing so complicated. You will notice that three months I liable as 2020 are actually jan-mar of 2021. 2. The goal is to make the first month of your fiscal year a 12. So if it's April, you add 8 (as I did). But if it's July, you add 5 as 7+5 is 12.
@@ExcelOlympics #1 what I mean is that for the months Jan to Mar of 2021, that would in what is called fiscal 2021. And the months April to December 2021, would be months 1 - 9 of fiscal 2022.
Thanks for sharing. For the Financial year and month can be calculated using Date.Month(Date.AddMonths([Date],-3)) to roll the calender month to Financial Month (April), after grouping by year the resultant table of tables can be worked to add index column and so the RT using List.FirstN and List.Sum. The solution can be shared
I have 25 yrs of weather Data that wonloaded in two columns, column A is date and time, Column B is Temperature. How do I sort this to get all of Jan data in a column and all of Feb data in another column, sort by month & date for all 12 months? This is a lot of Data.
You create a column with a month name and then pivot that column where values are in the temperature column. I suppose you also create a day of month column before and remove the date time column.
Wow Gasper, thank you very much! Super video! This helps me a lot. I love how understandable you explain complicated issues. Really great!🤩
Thank you for those kind words Dennis. I hope this helps you and if you have any additional questions you know where to find me 😀.
Brilliant video again Gasper!- really like how you manipulated the dates then use the integer divide 👌
Thanks Martin. Integer divide is an art form when you need it :).
Thank you for sharing your knowledge
and simple files
Thank you very much... because from the video you can follow it step by step easily using example files
Great videos, thanks! Any plans of doing this video for powerpivot?
Thanks for those kind words Monica. I sure can do one. It wasn't planned but as I am currently recording a "Time Intelligence in Excel and Power BI Data Models" Video Course I could post a "YTD snippet" to UA-cam.
Great video. Can't help but think there is a simpler way, but I'm sure you tried to find something easier.
2 things -
1) in some countries/organizations, the fiscal year is referred to by the ending year (so the 2020-2021 year is called 2021), how would you change your powerquery?
2) how would you change this for a different starting month? would be nice to have a parameter so that it could be changed dynamically.
Hi Ron.
1. That is exactly what I did and that made the whole thing so complicated. You will notice that three months I liable as 2020 are actually jan-mar of 2021.
2. The goal is to make the first month of your fiscal year a 12. So if it's April, you add 8 (as I did). But if it's July, you add 5 as 7+5 is 12.
.... and you didn't say "Brilliant" even once :-)
Really??? I guess I must have been sick or something 😀 because if anything having this ability in Power Query is BRILLIANT 🤣.
@@ExcelOlympics #1 what I mean is that for the months Jan to Mar of 2021, that would in what is called fiscal 2021. And the months April to December 2021, would be months 1 - 9 of fiscal 2022.
Oh I get it. So I went one under but that would just ve a simple +1 on my solutions fiscal year column. Thanks for pointing that out Ron.
Thanks for sharing. For the Financial year and month can be calculated using Date.Month(Date.AddMonths([Date],-3)) to roll the calender month to Financial Month (April), after grouping by year the resultant table of tables can be worked to add index column and so the RT using List.FirstN and List.Sum. The solution can be shared
Thanks for adding that technique Kgas...
I have 25 yrs of weather Data that wonloaded in two columns, column A is date and time, Column B is Temperature. How do I sort this to get all of Jan data in a column and all of Feb data in another column, sort by month & date for all 12 months? This is a lot of Data.
You create a column with a month name and then pivot that column where values are in the temperature column. I suppose you also create a day of month column before and remove the date time column.