Forum Discussion
How to compare date when adding custom column
Ha - I don;t know why I didn't think of this sooner!! :)
Instead of creating a Custom Column, I tried using the "Conditional Column".
After entering the necessary values it managed to create my FinancialYear column, and I could then see the actual code in the query editor, as follows:
= Table.AddColumn(#"Changed Type6", "FinancialYear", each if [DateCaseCreated] <= #datetime(2016, 6, 30, 0, 0, 0) then "FY2016" else "FY2015" )
Woohoo!!! :)
Hi,
The best solution, is to create a date dimension table with Fiscal calendar columns, like Fiscal year, Fiscal Month etc.
Import that, add it to you model and link the date fields.
This is also future-proof, as opposed to your formula, which you will have to change next year.
Another option is to change your formula that you just fixed, to work with any year.
Thanks Edgar - I did see soem articles regarding the creation of a date table - admittedly though I need to do some more reading up on this concept, thus my hope for a "quick" solution for now. WIll defo spend more time on understanding the Date Table concept though.