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

     

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      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.