cancel
Showing results for
Did you mean:
Helper III

Dynamic week end date

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Frequent Visitor

Re: Dynamic week end date

Lots of suggestions here

Community Support

Re: Dynamic week end date

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
5 REPLIES 5
Super User I

Re: Dynamic week end date

Hi Joseph,

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Super User IV

Re: Dynamic week end date

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.

Proud to be a Super User!

Power BI Blog
Helper III

Re: Dynamic week end date

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

Frequent Visitor

Re: Dynamic week end date

Lots of suggestions here

Community Support

Re: Dynamic week end date

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Announcements

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors