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

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

Was able to do with Custom Column as well using the code as

 

[DateCaseCreated] = #date(1900, 1, 1) then "FY2016" else "FY2015"

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors