- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to create a new formatted YYYY-YY column based on the row's date month value?
Hi,
In a single table I have a date column named 'Filing Date'. I would like to create a new column, in the same table, using the following logic.
If the Filing Date's month is 6 or greater (so 6 to 12) then in a new column (named 'Filing Year') place the current year's YYYY with the next year's YY, otherwise (so for months 1 to 5) place the previous year's YYYY with the current year's YY.
The below screenshot shows the Filing Date column with the intended Filing Year column.
I would like to achieve this using Power Query rather than DAX. I have seen M code, can't remember where now though, to achieve similar - so I do believe it is possible with a single line of M code rather than completing multple manual steps.
Thanks in advance.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @D_PBI ,
Lot of work! I wonder if you meant 7 or greater thus splitting the year in half. If so, change the code at the beginning replacing the 6 with 7.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
if Date.Month ([Column1])<6 then Text.Combine({
Text.From( Number.From( Text.End( Date.ToText([Column1] ),4))-1),Text.From( Number.From( Text.End( Date.ToText([Column1]),2)))},"-" ) else Text.Combine({
Text.From( Number.From( Text.End( Date.ToText([Column1] ),4))),Text.From( Number.From( Text.End( Date.ToText([Column1]),2))+1)},"-" )
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @D_PBI ,
Lot of work! I wonder if you meant 7 or greater thus splitting the year in half. If so, change the code at the beginning replacing the 6 with 7.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
if Date.Month ([Column1])<6 then Text.Combine({
Text.From( Number.From( Text.End( Date.ToText([Column1] ),4))-1),Text.From( Number.From( Text.End( Date.ToText([Column1]),2)))},"-" ) else Text.Combine({
Text.From( Number.From( Text.End( Date.ToText([Column1] ),4))),Text.From( Number.From( Text.End( Date.ToText([Column1]),2))+1)},"-" )
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,
Think that when using 'Number' component, you'll hit problems with leading zeroes. I.e. 2008/09 will appear as 2008/9.
Alternate method (Australian Financial Year Jul-Jun) -
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks @Nathaniel_C - this is much appreciated.
For others who may view this solution, for me, the only addition I needed to apply is to just add 'each' before the 'if' at the beginning of the M code.
Thanks again for your speedy help and Kudos for sure 👍
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@D_PBI ,
You are welcome! It woulde have been better if I mentioned that I built this in a custom column, and the each is not required. Power Query puts it in for you, althought it is good practice to put it in even so.
Cheers,
Nathaniel
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@Nathaniel_C - I knew you hadn't left of the 'each' by mistake. I knew in your example you didn't need it. I added the 'each' comment in case others applied the code directly in Advanced Editor and couldn't understand why it raised an error.
Contributions like yours are very important to the community so keep up the good work.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @D_PBI ,
Yes, thank you for your kind words. I was ok with it, just adding further clarification as you had, which I thank you for doing, sometimes I work so fast to get these answers posted, things get left out!😊
Nathaniel
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
02-08-2024 03:09 PM | |||
03-20-2024 01:56 AM | |||
02-20-2024 12:24 PM | |||
11-21-2023 08:06 AM | |||
07-12-2024 05:14 AM |
User | Count |
---|---|
27 | |
25 | |
25 | |
13 | |
9 |
User | Count |
---|---|
24 | |
19 | |
16 | |
13 | |
10 |