cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JayTG123
Advocate I
Advocate I

Create new column for Fiscal Year

Hi all,

 

I have the following data in Power Bi  - I have seperated the three columns up individually however I wish to create a new year column that will display the year like 2021/22 (UK Financial Year) The year starts from April - March. I have tried other methords I found on these forums but I am unable to replicate the year followed by the forward slash / and then then the 22 afterwards.

 

Screenshot 2021-11-30 193834.jpg

 

1 ACCEPTED SOLUTION
Nathaniel_C
Super User
Super User

Hi @JayTG123 ,
Go to Power Query, select Conditional Column, insert the code:

if [Offense date.2] >= 4 then Text.Combine({Text.From([Offense date.3]),Text.From([Offense date.3]+1-2000)},"/") else Text.Combine({Text.From([Offense date.3]-1),Text.From([Offense date.3]-2000)},"/")


Pics to follow.


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
Nathaniel_C
Super User
Super User

Hi @JayTG123 ,
Go to Power Query, select Conditional Column, insert the code:

if [Offense date.2] >= 4 then Text.Combine({Text.From([Offense date.3]),Text.From([Offense date.3]+1-2000)},"/") else Text.Combine({Text.From([Offense date.3]-1),Text.From([Offense date.3]-2000)},"/")


Pics to follow.


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Hi @JayTG123 Here are the steps:
Go to Power Query

Select Add Column

Select Custom Column

Add a title for the column

Paste the code into the box click ok

Change the column type to Text


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

Nathaniel_C_0-1638308540310.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Nathaniel_C  thank you so much for responding!! I have done exactly as you said but I am now getting an error message in the new column.. please see below:

 

Expression.Error: We cannot apply operator >= to types Record and Number.
Details:
Operator=>=
Left=
Offence date.2=10
Right=4

Hi @JayTG123  as it looks like you split out the date into these three columns, I assumed that they are whole numbers, am I correct?
Thank you,


Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Nathaniel_C Yes this was a date column (eg: 30/11/2021) but I seperated it out into three columns - did I need to do this ? 

After I split the columns I then changed the type to Whole Number. 

@JayTG123 I sent a link to my pbix, maybe that will be helpful.


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




THANK YOU !!! @Nathaniel_C  Its worked and thank you for taking the time to send me your test file over. 😀

 

Jay

Hi @JayTG123, you are most welcome!  If you had kept it as a date column, you could have done something like Date.Year() to get the year from the date. Let me recommend "Master Your Data" by @KenPuls and Miguel Escobar!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors