Forum Discussion

Danbert's avatar
Danbert
Frequent Visitor
6 years ago
Solved

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 t...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)
Danbert's avatar
Danbert
Frequent Visitor
6 years ago
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's avatar
Danbert
Frequent Visitor
6 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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)
    • Danbert's avatar
      Danbert
      Frequent Visitor
      6 years ago

      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's avatar
      Danbert
      Frequent Visitor
      6 years ago

      Mike, that worked perfectly, thank you so much.

       

      Dan