Conditional Column with values for fiscal year date ranges

[Cre8ive]

How do I create a conditional column with values for fiscal year date ranges "9/1/2011 to 8/31/2012"?

[HotChilli · Accepted solution]

Something like this:

if Date.Month([Column1]) < 9 then Date.Year([Column1]) else Date.Year([Column1]) + 1

Please check at your side because I just rushed it.

It's generally considered good practice to do this sort of thing in a date table and use a relationship to link to the data.  Plenty of resources out there. 

[Cre8ive]

It worked, thank you!!!  Good to know, I will look into date tables.

[HotChilli]

Could you provide more detail please?  Do you want to add a column in Power Query according to a date in your data already? or something else?

[Cre8ive]

Yes, in Power Query, I have a Transaction Date column and want to add a column that calculates Fiscal Year based on the transaction date ranges.

In Excel I used the formula below, I tried to replicate it in a Conditional Column and  then tried copying it into a Custom Column, neither worked.

=TEXT(YEAR([@[Transaction Date]])+IF(MONTH([@[Transaction Date]])>8,1,0),0)