Forum Discussion
How to use date function in statment
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 |
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.
4 Replies
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.
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!!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]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.
