Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kalyan1106
Regular 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
Anonymous
Not applicable

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
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.