powerbidaxdata-analysispowerbi-desktopbusiness-intelligence

Strange behaviour of Functions with Dates

I am having a brain freeze.

I imported SampleA.csv and then I tried the below on a date field

EOMONTH(SampleA[Date],0)

And

ENDOFMONTH(SampleA[Date])

They both should give me the same value. However, it doesn’t.

enter image description here

First, I thought it was bug, so I tested it with a new file SampleB.Csv and it works as expected.

I am unable to understand what the issue could be. I tried in 2 different pcs and same issue was observed in both.

enter image description here

Solution

  • ENDOFMONTH will return the highest day of the month from your Dates column. Days that aren't present on that column will not count.
    So in your example, it looks like it works in SampleB because you added a row with Feb 29th, while the highest for SampleA is Feb 28th.
    It is recommended to have a Dates table with all the days to avoid this.
    Source: https://dax.guide/endofmonth/

    On the other hand, EOMONTH will return the last day of the specified month, regardless of the data in your Dates column.
    Source: https://dax.guide/eomonth/