Forum Discussion

smokeyjoe03's avatar
smokeyjoe03
Frequent Visitor
3 years ago

Strange DATE Function Behaviour

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.



Date
DAX

6 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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's avatar
      smokeyjoe03
      Frequent Visitor
      3 years ago

      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's avatar
        FreemanZ
        Super User
        3 years ago

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

        try to change the month part to :

        IF(

          [MonthofYear ]=1,

         12,

         [MonthofYear ]-1

        )

  • Bujor's avatar
    Bujor
    Frequent Visitor
    3 years ago

    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 ) )
    )