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) ...
Date
DAX
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

      )

      • smokeyjoe03's avatar
        smokeyjoe03
        Frequent Visitor
        3 years ago

        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)