cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wi11iamr
Advocate II
Advocate II

How to compare date when adding custom column

I'm trying to create a Custom Column for the Financial Year of a company.

 

The below extract is just trying to test how I can compare two dates within my query editor.

= Table.AddColumn(#"Changed Type6", "FinancialYear", each if [DateCaseCreated] >= 2015/07/01 then "FY2016" else "FY2015")

 

I have tried multiple formats for my date ", ', [, (

I've also tried using functions like DateValue(2016/05/30) - nothing is working

 

In my naive opinion, all I need to do is understand what format/syntax I need to write my 2016/06/30 date in.

this is beyond frustrating - I'm obvioulsy missing something key... 

 

 

 

 

1 ACCEPTED SOLUTION
wi11iamr
Advocate II
Advocate II

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!!! 🙂

View solution in original post

3 REPLIES 3
waltheed
Solution Supplier
Solution Supplier

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.

Cheers, Edgar Walther
ITsmart BI and Analytics consultant
Power BI User Group Netherlands

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.

wi11iamr
Advocate II
Advocate II

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!!! 🙂

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.