cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Advocate II
Advocate II

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

View solution in original post

3 REPLIES 3
Highlighted
Advocate II
Advocate II

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

View solution in original post

Highlighted
Solution Supplier
Solution Supplier

Re: How to compare date when adding custom column

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
Highlighted
Advocate II
Advocate II

Re: How to compare date when adding custom column

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors