Strange DATE Function Behaviour

[smokeyjoe03]

Hi all, looking for a sanity check and hopefully an explanation on the below.

I have a Date table with:
Column Name (Column Type)

Date (Date)

Year (Whole Number)

MonthOfYear (Whole Number)

If I create a calcualted column with the data type Date using the following DAX...

NewColumn = DATE(2022,11,30)
...it returns 30 November 2022. DATE accepts 3 whole numbers as inputs and correctly displays the date, brilliant, all good.

However, if I use the following DAX, I get an error:
NewColumn = 

DATE(

'Date'[Year], --Passes a whole number to DATE

'Date'[MonthOfYear] -1, --Passes a whole number to DATE

DAY( --Passes a whole number to DATE

ENDOFMONTH( --Passes a date to DAY

DATEADD( --Passes a date to ENDOFMONTH

'Date'[Date], -1, MONTH --Passes a date to DATEADD

)

)

)

)

As you can see from the comments, DATE is being passed 3 whole numbers (2022, 11 and 30 for Today's date), however I'm getting the following error:

I have tried hardcoding the Day argument of the DATE function and leavng the Year and Month as is and that then works correctly, which means Power BI doesn't like something about that section. So I've created a column with just that section of the code and it returns 30 as expected and Power BI autodetects that it is a whole number.

I've also created the 3 columns seperately and can confirm they are returning whole numbers of 2022, 11 and 30 so the error can't be correct. All three arguments are the correct type and the values are neither too long or too short.

Can anyone explain why passing 3 whole numbers to the DATE function works when they are hardcoded but not in the second example? Or is this potentially a bug?

Thanks for taking the time to read my post.

[Bujor]

Your calculated column return a month of zero whenever the date is in January

The error is in the "months" parameter of the DATES function - the result is zero.

My solution is to add two intermediary steps:

Step1:

NewColumn_Step1 =

CALCULATETABLE(

    LASTDATE( 'Date'[Date] ),

    PREVIOUSMONTH( 'Date'[Date] )

)

Step2:

 New Column_Step2 =

IF( ISBLANK( 'Date'[NewColumn_Step1] ), EOMONTH( 'Date'[Date], -1 ), 'Date'[NewColumn_Step1] )

Final:

NewColumn =

DATE(

    YEAR( 'Date'[New Column_Step2] ),

    MONTH( 'Date'[New Column_Step2] ) - 1,

    DAY( EOMONTH( 'Date'[New Column_Step2], -1 ) )

)

[FreemanZ]

Hi @smokeyjoe03 

DAY might not accept an expression, but Date value only. 

If you are trying to get last day of the previous month, you may try this:

New Column = EOMONTH ( 'Date'[Date], -1 )

[smokeyjoe03]

Hi @FreemanZ , thanks for the reply.

As I explained in the original post, I've already tested all 3 arguments seperately to ensure they work. As you can see here:

Which returns:

So DAY definitely accepts expresions.

Thanks for your suggestion, the column I was building was for a different reason which I have since resolved, I'm just looking for an explanation as to why DATE doesn't like the 3 whole number arguments being passed to it.

[FreemanZ]

In case MonthofYear =1, could it be an issue?

try to change the month part to :

IF(

  [MonthofYear ]=1,

 12,

 [MonthofYear ]-1

)

[smokeyjoe03]

@FreemanZ thanks for trying to help but again, I've troubleshot that as I explained in the original post. 

All three of those arguments work independantly. I've confimred this by creating a column using the YEAR, MONTH and DAY functions individually.

I've also used them one at a time and two at a time in conjunction with hard coded values (as below)

The only time there is an error is when the DAY function is used for an argument, but that function works independatnly to return a who;e number (which is all DATE requires)

[FreemanZ]

i like you way of learning. I followed your way and tried the following:

you see, if there is for the first month of the Date Column (as there is no previous month), so BLANK is returned. And DATE can't take BLANK as its argument, so come the error you see.