Elsa has attended:
Access Intermediate course
Power BI Modelling, Visualisation and Publishing course
Month-YY order
Hi
Please could I seek advice for the following:
[Task Month] - e.g. Dec
[YY] - e.g.21
[MMM-YY] - e.g. Dec-21
[Task MonthNo] - e.g. 12
[Task MonthOrder] - e.g. 5 (as Financial year starts from Aug to July, therefore Dec is the fifth month in the FY)
I am able to make the month order based of [Task MonthOrder], however I have various months and years in the report e.g. Dec-19, Dec-20, Dec-21.
How can I sort out the order of Aug-20, Sep-20, Oct-20 etc to ensure they are before Aug-21, Sep-21, Oct-21 etc.?
Many thanks
Elsa
RE: Month-YY order
Hi Elsa,
Thank you for your question.
I need to check what exactly you want to know. What is the order you want to achieve for your Month-YY dates? Is it Fiscal Month 1st then Year 2nd? eg.
Aug-20
Aug-21
Sep-20
Sep-21
Please let me know if it's this or something else you would like to solve
Kind Regards
Martin
RE: Month-YY order
Thanks Martin for your help in advance. I would like the order like the following:
Aug-19, Sep-19, Oct-19....Jul-20, Aug-20, Sep-20......Aug-21, Sep-21, Oct-21 and so on.
As now I can only use the order of month: Aug = 1, Sep = 2...but how can I order them in the format of MMM-YY.
Many thanks
Elsa
RE: Month-YY order
Hi Elsa,
There is no easy solution and it cannot all be done in Power BI as far as I know. What you will need to do is firstly create a 'YearMonth' key in the date table of Excel (see the 'date table' file we used in the 'Sales Data' file). This key will need to show the following:
20190100 for Aug-19
20190200 for Sep-19
20190300 for Oct-19
20190400 for Nov-19
20190500 for Dec-19
20200100 for Jan-20
Thereafter the key adds 100 for each new month and so on. The trick is to use an IF formula that says if it's the first year use the fiscal month series: Aug=1, Sep=2 etc. otherwise use the normal month series: Jan=1, Feb=2 etc. The formula is:
=IF(YEAR(date cell)=MIN(YEAR(date range)),(YEAR(date cell)*10000+MONTH(EDATE(date cell,5))*100),YEAR(date cell)*10000+MONTH(date cell)*100)
Copy down the formula for the remaining dates and once file is saved and closed, refresh in Power BI desktop. Create a slicer with the MMM-YY date field and then in the 'Sort Column by' drop down select the 'YearMonth' key.
This will only work if your dates start Aug 2019 onwards
I hope this works for your particular dataset and please let me know if it does.
Kind Regards
Martin
RE: Month-YY order
Thanks Martin, I will have a go later and will let you know does it work.
Have a good weekend.
Regards
Elsa
RE: Month-YY order
Hi Elsa,
Have a great weekend and I'll wait to hear how you get on next week
Kind Regards
Martin
RE: Month-YY order
Hi Martin
The formula works perfectly! Many thanks for your help.
Regards
Elsa
RE: Month-YY order
Hi Elsa,
You're welcome.
Have a good evening
Kind Regards
Martin
