Dynamic week end date

[joschultz]

Looking for a way to create a calculated column that will change the week ending date starting with Yesterday.  So for instance

Date	Week End
9/2/2017	9/2/2017
9/1/2017	9/2/2017
8/31/2017	9/2/2017
8/30/2017	9/2/2017
8/29/2017	9/2/2017
8/28/2017	9/2/2017
8/27/2017	9/2/2017
8/26/2017	8/26/2017
8/25/2017	8/26/2017
8/24/2017	8/26/2017
8/23/2017	8/26/2017
8/22/2017	8/26/2017
8/21/2017	8/26/2017
8/20/2017	8/26/2017
8/19/2017	8/19/2017
8/18/2017	8/19/2017
8/17/2017	8/19/2017
8/16/2017	8/19/2017
8/15/2017	8/19/2017
8/14/2017	8/19/2017
8/13/2017	8/19/2017

But then when the date changes to tomorrow it will basically shift everything one day.   so then the week end will start on 9/3/17 and then go back from there.

Thank you,

Joseph

[DSP · Accepted solution]

Lots of suggestions here

[Anonymous · Accepted solution]

Hi @joschultz,

I'd like to share a simple formula to get current week end date.(based on your data, it seems like saturday?)

Calculate column formula:

Week End = [Date]+ 7-WEEKDAY([Date],1)

Notice: weekday will get day of week.(1~7, 1= sunday, 7 saturday), if you want modify the week end date, you only need to modify '7' to which you wanted.

Regards,

Xiaoxin Sheng

[GilbertQ]

Hi @joschultz

What I would suggest doing is to create a column in the Query Editor instead of a calcuated column in the Power BI Data model. It is a lot easier to do this.

For your example you could create the column in the Query Editor with the following syntax. In the Query Editor you can click on the Add Column Ribbon and then select Custom Column.

Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1)

That will ensure everytime you refresh your data, it will get the previous date.

[joschultz]

It is a date dim table.  I can refresh it every day which is what I will do.  I have a solution using a relative day field and then using if then statements but its not dymanic in the sense that I need to create a huge if then statment to go back the amount of time I want to.  Which is fine but looking for something easier.  Here is what I have done so far. 

Calculated Column for Relative day

a Relative Day = datedim[DAY_IN_YEAR_NUM] - CALCULATE(SUM(datedim[DAY_IN_YEAR_NUM]),ALL(datedim),TODAY()=datedim[DATE_VALUE])

Then to change the week end date.

Week End = IF( AND(datedim[Relative Day]<0,datedim[Relative Day]>-8),TODAY()-1,IF( AND(datedim[Relative Day]<-7,datedim[Relative Day]>-15),TODAY()-8,IF( AND(datedim[Relative Day]<-14,datedim[Relative Day]>-22),TODAY()-15,IF(AND(datedim[Relative Day]<-21,datedim[Relative Day]>-29),TODAY()-22,IF(AND(datedim[Relative Day]<-28,datedim[Relative Day]>-36),TODAY()-29,IF(AND(datedim[Relative Day]<-35,datedim[Relative Day]>-43),TODAY()-36,IF(AND(datedim[Relative Day]<-42,datedim[Relative Day]>-50),TODAY()-43,TODAY()-50)))))))

But I want to go back two years and while I can keep the if statement going.  I would rather try and find a simplier solution.

Thank you,

Joseph

[DSP · Accepted solution]

Lots of suggestions here

[Anonymous]

Hi Joseph,

Please calrify.

a) How is this table populated ?

b) Is this a Calendar Table that gets dynamically populated with starting date as (????????) and ending date as today ?

Let me know then I can try to work out a solution for you.

Cheers

CheenuSing