Search code examples
powerbidaxpowerbi-desktop

Power BI DAX, END OF MONTH DOESN'T WORK WITH DATE TABLE

When using endofmonth dax statement with my fact table it works:

EOM = 
CALCULATE(
    SUM('Table'[sales]),
    ENDOFMONTH('Table'[date])
)

However when I use it with my date table it returns blanks does anyone know why?

dax command using my date table:

EOM_DTBLE = 
CALCULATE(
    SUM('Table'[sales]),
    ENDOFMONTH('DATE'[Date])
)
Solution

  • You need to check what end of month returns first:

    If we test it with a simple code:

    Test = CALCULATE(ENDOFMONTH('Date'[Date]))

    FRST

    So basically, It returns the last date of each month. If you have no data in the end of the month specified, It returns empty.

    If you have data (I have 679 on 31/01/2022) then, Code returns:

    FDF