cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Danbert Frequent Visitor
Frequent Visitor

Working with non-standard date fields

I have 2 columns in my dataset - these columns are used to show the financial month and year period. Because of this they are not formatted in a friendly way for use in formulas so I have a need to transform the data.

 

Column 1 (Month) is in the format "<month_number>-<month_name>". This is based on our financial year, so May is month 1. So values show as, "01-May", "02-June", through to "12-April".

 

Column 2 (Year) shows the year, but in the format "18/19", "19/20", as the financial year straddles 2 years.

 

I need to standardise the data in these fields in so I can compare with other date fields that are in standard date format.

 

I know I can use a delimiter to split the Month column so it can simply show as Jan, Feb, Mar etc., but the battle is assigning the correct year to each month.

 

Based on year 18/19, the logic basically is that if the month is 8 (December) or lower, the year needs to return the lower value (i.e. 18). If the year is 9 (January) or higher, it needs to revert to the higher value (i.e. 19).

 

Any assistance would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
mcybulski Established Member
Established Member

Re: Working with non-standard date fields

Hi Danbert,

 

I am confused. The formula below (which is from the code above) does what you are asking and does not require editing.

 

Regards,

Mike

 

Date.AddMonths(#date(2000+Number.From(Text.Start([Year],2)),Number.From(Text.Start([Month],2)),1),4)

View solution in original post

6 REPLIES 6
mcybulski Established Member
Established Member

Re: Working with non-standard date fields

Hi Danbert,

 

This problem is easier then it appears on first glance. The approach is to 1) create a date where the year is the first two digits of the Year and the first two digits of the month. This now converts May to Jan-2018, June to Feb-2018, ..., March to Nov-2018 and April to Dec-2018. Now 2) add four months to the date to get May-2018,Jun-2018, .., Mar-2019 and Apr-2019. The method is implemented as a custom column below.

 

Regards,

Mike

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddYear = Table.AddColumn(Source, "Date", each Date.AddMonths(#date(2000+Number.From(Text.Start([Year],2)),Number.From(Text.Start([Month],2)),1),4))
in
    AddYear

 

 

 

Danbert Frequent Visitor
Frequent Visitor

Re: Working with non-standard date fields

Hi Mike
Many thanks for your reply.
 
I managed to get around the year issue by adding a custom column (I am using Power BI).  It will need editing as time goes by but it will suffice for now.
=if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "18/19"
then 2018
else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "18/19"
then 2019
else if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "19/20"
then 2019
else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "19/20"
then 2020
else if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "20/21"
then 2020
else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "20/21"
then 2021
else if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "21/22"
then 2021
else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "21/22"
then 2022
else 0
I have split the month field as mentioned so I now have a 'Standard Month' column (text formatted) with 'January, February, March etc...', and a 'Standard Year' column (date formatted) with '2019, 2020, 2021 etc...'
 
What I need to do now is combine those fields into a single date field, so I can compare with other date fields.  If the format has to include a day of the month then that's fine (I would just have the first day of each month).  It would be great if you could point me in the direction of how to do that?
Danbert Frequent Visitor
Frequent Visitor

Re: Working with non-standard date fields

I created a further custom field (converting the Months to Numbers), merged the new field and the Year field together and converted to date format.  They showed up in the format 01/02/2019, 01/03/2019 etc, but perfect for my needs.

mcybulski Established Member
Established Member

Re: Working with non-standard date fields

Hi Danbert,

 

I am confused. The formula below (which is from the code above) does what you are asking and does not require editing.

 

Regards,

Mike

 

Date.AddMonths(#date(2000+Number.From(Text.Start([Year],2)),Number.From(Text.Start([Month],2)),1),4)

View solution in original post

Danbert Frequent Visitor
Frequent Visitor

Re: Working with non-standard date fields

Hi Mike

 

Apologies, I'm still new to this and managed my own rudimentary solution to get me past a block.  Your initial post referenced an excel worksheet and I'm not clear on the syntax to amend that to match my situation in a query in Power BI.  I will try your amended suggestion also when I get a chance and will post back and mark as a solution if it works for me.

 

Thanks again.

Danbert Frequent Visitor
Frequent Visitor

Re: Working with non-standard date fields

Mike, that worked perfectly, thank you so much.

 

Dan

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors