Power Pivot - Day Function

[Skovgaard]

Hi,

In my Data Model I have a custom column, which calculates a date. I use Date(Year(xxx),Month(xxx),Day(xxx)) to calculate the date.
I'm having trouble with the Day function, which doesn't convert to correct day in month.

I have a table, where the DAY function should pick up a number and then convert it to the day in a month, so the total date is set.
See below, when I only use Related, it picks up 2, when I use DAY it converts it to 1.

Why doesn't the day function convert it to 2?


Below function picks up the number 2

Below function returns the number 1

/Skovgaard

 

[RoryA]

The DAY function expects you to pass it a date. If you pass it the value 2 as you are, it interprets that as 2 days starting from 31/12/1899 since that is how Excel stores dates, which is 1/1/1900 and hence you get 1 as the day number.

 

[Skovgaard]

The DAY function expects you to pass it a date. If you pass it the value 2 as you are, it interprets that as 2 days starting from 31/12/1899 since that is how Excel stores dates, which is 1/1/1900 and hence you get 1 as the day number.

Thanks for your reply, I was wondering why it behaved differently in Excel and in the Data Model.
However I made a workaround in my DimensionTable, so fixed it in that way.

/Skovgaard

 

[RoryA]

It's odd because according to the DAX documentation, valid dates start at 1 Mar 1900