How to create column for last date of every week? I have columns: year, month_number and week_number

[kalyan1106]

Hi,

I want to create a new column which gives me the last day of every week. I already have year_number, month_number, and week_number.

Thank you.

[Anonymous · Accepted solution]

Try the following code:

It calculates the max date for each weeknumber and year combination.

Last day of week =
CALCULATE (
    MAX ( 'Calendar'[Date] );
    FILTER (
        'Calendar';
        'Calendar'[Week number] = EARLIER ( 'Calendar'[Week number] )
            && 'Calendar'[Year] = EARLIER ( 'Calendar'[Year] )
    )
)

If you have the ability to use the power query editor to do this use the Date.EndOfWeek() function in a custom column.

Date.EndOfWeek([Date])

Kind regards
Joren Venema

Data & Analytics Consulant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.