last date in a week

[RMV]

Hi,

I need to create a report grouped per week.

And the week is calculated with Saturday as the first day of the week.

The week must be displayed with the last date of the week. What formula suitable for this?

This is the result I need:

Date            Week

1-Jan-17     W1-1/06/2017

2-Jan-17     W1-1/06/2017

3-Jan-17     W1-1/06/2017

4-Jan-17     W1-1/06/2017

5-Jan-17     W1-1/06/2017

6-Jan-17     W1-1/06/2017

7-Jan-17     W2-1/13/2017

8-Jan-17     W2-1/13/2017

9-Jan-17     W2-1/13/2017

10-Jan-17   W2-1/13/2017

11-Jan-17   W2-1/13/2017

12-Jan-17   W2-1/13/2017

13-Jan-17   W2-1/13/2017

14-Jan-17   W3-1/20/2017

etc

Please kindly help. Thanks.

[CheenuSing]

Hi @RMV

Try this

this should work

WeekEndDate = Date([Year],1,1) - WeekDAY( Date([Year],1,1),2 ) +WEEKNUM([Date])* 7

Where [Year] is column in your Calendar table =  YEAR(Calendar[Date])

[Date] is the date in your Calendar table.

If it works for you please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

[RMV]

@CheenuSing

When I used it for grouping, it splitted to 2 for same week, e.g.

W1 - 1/7/2017

W2 - 1/7/2017

W2 - 1/14/2017

W3 - 1/14/2017

W3 - 1/21/2017

etc

I need it to be grouped per week, i.e.

Jan - W1 - 1/6/2017

Jan - W2 - 1/13/2017

Jan - W3 - 1/20/2017

Jan - W4 - 1/27/2017

Jan - W5 - 1/31/2017

Feb - W1 - 2/3/3017

Feb - W2 - 2/10/2017

etc

[CheenuSing]

Hi @ RMV

This requires a different approach.  Need some time to arrive at a solution.

Cheers

CheenuSing

[CheenuSing · Accepted solution]

Hi @RMV

Here you go

1. Create a column to find the Week Number for a month based on 1st of the month with Fri as week ending day.

NewWeekNo =
VAR WeekNumFri = WEEKNUM('Calendar'[Date],16)                               /* Calculate the Week number bsed on Friday Week End */

VAR MonthWeekNoFri = WEEKNUM(DATE([Year],[MonthNumber],1),16)
                                                                 /* Generate the WeekNumber of the First day of Month */

Return

WeekNumFri - MonthWeekNoFri + 1

2. Compute the WeekEndDate as follows

WeekEndDate = IF ( MONTH(Date([Year],1,1) -WeekDay( Date([Year],1,1),16) +WEEKNUM([Date],16)* 7)  
                                     <>          Month([Date])  ,
                                                        EOMONTH([Date],0)    ,
                                                                          Date([Year],1,1) -WeekDay( Date([Year],1,1),16) +WEEKNUM([Date],16)* 7 ) 

Jan 2016Aug 2016

Sample table based on the above formulas.

It will work.  

If it resolves please accept this as a solution and also give KUDOS.

Cheers 

CheenuSing

[RMV]

Amazing! It works correctly!

Thanks @CheenuSing!

[RMV]

Hi @CheenuSing,

Tried it, and it shows Sunday's date as last date of the week.

How to show Friday's date as the last date of the week?

I tried some calculations, but haven't got the solution yet.

Thanks

[CheenuSing]

Hi @RMV

Use this for Friday as last date of the week.

WeekEndDate = Date([Year],1,1) -WeekDay( Date([Year],1,1),16) +WEEKNUM([Date],16)* 7

Change 16 in WeekDay and WeekNum function as follows for different week begin date.

  Number   - Week Begins  - Week Ends

1 or 17  -   Sunday     - Saturday

2 or 11 - Monday      - Sunday

12  - Tuesday            -  Monday

13 - Wednesday       - Tuesday

14 - Thursday            - Wednesday

15 - Friday                - Thursday

16 - Saturday            - Friday

Cheers

CheenuSing

[v-qiuyu-msft]

Hi @RMV,

You can create a calendar table, then create a calculated column like below to return last date within each week. Please see attached .pbix file.

LastDate = CALCULATE(MAX('Calendar'[Date]),FILTER(ALL('Calendar'),YEAR('Calendar'[Date])=YEAR(EARLIER(Table1[Date])) && EARLIER('Table1'[Week])='Calendar'[Week]))

Best Regards,
Qiuyun Yu

[RMV]

Hi @v-qiuyu-msft,

I cannot get the result.

I saw that there are 2 tables in the calculation. Is it possible to only calculate the Week from Calendar table only?

This is to show consistency of weeks to show based on calendar date.

Thus, weeks with no data can still be shown.

Thanks

[v-qiuyu-msft]

Hi @RMV,

You can create a column to return last date of each week based on the exiting date:

Column 2 = CALCULATE(MAX('Table1'[Date]),FILTER(ALL('Table1'),'Table1'[Week]=EARLIER(Table1[Week]) ))

But as you can see, for the week 3, the returned last date is 2017-1-14 as there are not complete the date within week 3. That's the reason why I need to create a calendar to be a reference.

In your scenario, if you want to return last date within each week based on existing date, then you can create a calculated column above. If you want to return last date of each week based on exact calendar, please try the suggestion in my previous reply.

Best Regards,
Qiuyun Yu

[MarcelBeug]

In Power Query, you can add a custom column with formula:

= "W"&Text.From(Date.WeekOfYear([Date],Day.Saturday))&"-"&Text.From(Date.EndOfWeek([Date],Day.Saturday))

[RMV]

Hi @MarcelBeug,

tried that, but got an error: The syntax is incorrect.

I tried to apply it in my dax formula.

Btw, the week I tried to get is week of each month.

I got the dax formula for this one already. I just need to get the last day of the week.

Please kindly help. Thanks

[MarcelBeug]

I can't help you with DAX formula's.