cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kalyan1106 Visitor
Visitor

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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

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.

View solution in original post

1 REPLY 1

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

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.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors