How to use date function in statment

[Anonymous]

Hello,

im trying to write if statment with date function, however im getting error . the porpose for the statment to give and date before "31/12/2009" as number 0 then give following date. below the statment i wrote in custome column power bi. & the tabel below as example from my source.

Base column= if (
[Sales-DATE]= null ) then

""
else
if (
Date.Day ( [Sales-DATE] ) <=Date.Day(2009,12,31)) then
0
else [Year.Base]

Example

Sales-DATE	Year.Base	Output
01/03/2014	2014	2014
Null	Null
20/05/2015	2015	2015
10/09/2008	2008	0

[Anonymous · Accepted solution]

Hi @Anonymous ,

What data type is your [Sales-DATE]?

If your [Sales-DATE] is text type, please see the following calculated column.

Base column =
VAR _month =
    RIGHT ( LEFT ( [Sales-DATE], 5 ), 2 )
VAR _day =
    LEFT ( [Sales-DATE], 2 )
VAR _year =
    RIGHT ( [Sales-DATE], 4 )
RETURN
    IF (
        [Sales-DATE] <> "Null",
        IF ( DATE ( _year, _month, _day ) <= DATE ( 2009, 12, 31 ), 0, [Year.Base] )
    )

Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

You can check the details in the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[themistoklis]

@Anonymous 

It seems that you are trying to achieve this using Power Query.

Go to Power Query editor --> Add Column --> Custom Column and add the following formula:

Add Custom Column = if [Sales-DATE] = null then "" else if [Sales-DATE] ) <=#date(2009, 12, 31) then 0 else [Year.Base]

[Jihwan_Kim]

Hi, @Anonymous 

Please try the below.

Base column = IF( 'Table'[Sales-DATE] = BLANK(), BLANK(),

IF('Table'[Sales-DATE] <= DATE(2009,12,31), 0, 'Table'[Year.Base]))

The above is for the calculated column.

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos!!

[Angith_Nair]

Hi @Anonymous ,

Try to use this code..

Column =
IF (
    ISBLANK ( Table[Sales-DATE] ),
    BLANK (),
    IF ( Table[Sales-DATE] <= DATE ( 2009, 12, 31 ), 0, Table[Year.Base] )
)

In place of "Table" write your table name.