Adding last day to each month

[David_1970]

I have a date column of the type 201701, 201702, 21703, i.e. only showing YearMonth.

How do I calculate a new column, based on the above column, also including the last day of each month?

[v-yulgu-msft · Accepted solution]

Hi @David_1970,

You may need an extra date table like this:

Dim date = CALENDAR(DATE(2017,1,1),DATE(2017,12,31) )

Then, filter above calendar table. In this filtered table, create a YearMonth column.

Dim Table2 =
CALCULATETABLE (
    'Dim date',
    FILTER (
        'Dim date',
        'Dim date'[Date]
            = CALCULATE (
                MAX ( 'Dim date'[Date] ),
                ALLEXCEPT ( 'Dim date', 'Dim date'[Date].[Year], 'Dim date'[Date].[Month] )
            )
    )
)

YearMonth = FORMAT ( 'Dim Table2'[Date], "YYYYMM" )      

In your source table which has a date column of the type 201701, 201702, 21703, etc. create a calculated column using below formula:

Last day of each month =
LOOKUPVALUE ( 'Dim Table2'[Date], 'Dim Table2'[YearMonth], TableName[YearMonth] )

Regards,
Yuliana Gu