topic Re: Formula Date doesn't work in DAX Commands and Tips

Formula Date doesn't work

JEJP — Mon, 06 Feb 2023 18:00:23 GMT

Hello,

I have made my Dax formula, but it still gives an error. It says that the An argument of function 'DATE' has the wrong data type or the result is too large or too small. What is wrong with my formula? Field GeboorteDatum (Birthdate) is a Date type.

Re: Formula Date doesn't work

tamerj1 — Mon, 06 Feb 2023 18:46:57 GMT

Hi JEJP

try by using MONTH ( ) and DAY ( ) functions

Re: Formula Date doesn't work

JEJP — Mon, 06 Feb 2023 18:57:25 GMT

Hi tamerj1 ,

I still get the same error:

Re: Formula Date doesn't work

tamerj1 — Mon, 06 Feb 2023 19:07:38 GMT

JEJP

Please try

Birthdaynext =
VAR Items =
SUBSTITUTE ( Medewerker[GebrooteDatum], "-", "|" )
VAR TheYear =
YEAR ( TODAY () )
VAR TheMonth =
PATHITEM ( Items, 2 )
VAR TheDay =
PATHITEM ( Items, 1 )
VAR ThisYearX =
IFERROR ( DATE ( TheYear, TheMonth, TheDay ), BLANK () )
VAR NextYearX =
IFERROR ( DATE ( TheYear + 1, TheMonth, TheDay ), BLANK () )
RETURN
IF ( TODAY () > ThisYearX, NextYearX, ThisYearX )

Re: Formula Date doesn't work

JEJP — Mon, 06 Feb 2023 19:16:31 GMT

Unfortunately, it still doesn't work.

Re: Formula Date doesn't work

Anonymous — Mon, 13 Feb 2023 06:39:15 GMT

Hi JEJP ,

I create a table to test, it works well, could you provide the desensitized example data for me to test.

Column = VAR _a = DATE ( YEAR ( TODAY () ), MONTH ( 'Table'[Column1] ), DAY ( 'Table'[Column1] ) ) VAR _b = DATE ( YEAR ( TODAY () ) + 1, MONTH ( 'Table'[Column1] ), DAY ( 'Table'[Column1] ) ) RETURN IF ( TODAY () > _a, _b, _a )

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Formula Date doesn't work

JEJP — Mon, 13 Feb 2023 08:17:49 GMT

Thank you! I have tried to fill out the blank fields (not every birthday was filled out in the system). Now it works!

Re: Formula Date doesn't work

JEJP — Mon, 13 Feb 2023 08:25:11 GMT

Thank you very much. I already found the solution, but this formula also works!