The last day of month from the last date with sales

Question

How can I get the last day of month from the last date with sales? I want the result to be returned as a DAX calculated table.

The first step, calculated table:

LastDate = LASTNONBLANK( T[Date], CALCULATE( SUM( T[Amount] ) ) )

It returns a table (one column, one row) with the last date with sales. So far, correct, as expected. Say the last day with sales is 2020-04-15. But I want the end of month so 2020-04-30.

I hoped this should work, but it doesn't.

LastDate =
ENDOFMONTH (
    LASTNONBLANK (
        T[Date],
        CALCULATE ( SUM ( T[Amount] ) )
    )
)

It still returns 2020-04-15, instead of expected 2020-04-30.

RADO RADO · Accepted Answer · 2020-04-15T19:38:15

The problem is caused by using dates from table T. Instead, you need to use dates from the calendar table, because list of dates for the time intelligence functions must be continuous.

Assuming you have a properly designed and connected calendar table 'Date' in your data model, change your DAX to:

LastDate =
ENDOFMONTH (
    LASTNONBLANK (
        Date[Date],
        CALCULATE ( SUM ( T[Amount] ) )
    )
)

ENDOFMONTH will use now a different table (Date), because LASTNONBLANK function will keep data lineage to it.

The last day of month from the last date with sales

1 Answers