Difference between DAX and excel Dates

[jak82]

Hi,

For some reason when I use

=TEXT([@PublicationDate], "dddd")

Against 26/06/2017 in a table it brings in Monday which is correct.

However if I use

=FORMAT(DAY('Calendar'[Date]),"dddd")

Against 26/06/2017 in a Table column in PowerPivot it brings in Thursday?

??? Any ideas whats going on?

 

[RoryA]

Remove the DAY function from the DAX version.

 

[jak82]

Thanks, Worked a treat. Why would the day affect function affect it?

 

[RoryA]

Day(26/06/2017) would return 26. You then try and format 26 as a day name. Since Excel and DAX treat dates as a number of days after 31/12/1899, you’re actually returning the day name for 26 Jan 1900.

 

[jak82]

Day(26/06/2017) would return 26. You then try and format 26 as a day name. Since Excel and DAX treat dates as a number of days after 31/12/1899, you’re actually returning the day name for 26 Jan 1900.

Cool, Thanks

 

[RoryA]

Glad to help.

You would have had the same problem had your Excel formula been:

=TEXT(DAY([@PublicationDate]), "dddd")