cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joschultz Helper III
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

 

DateWeek End
9/2/20179/2/2017
9/1/20179/2/2017
8/31/20179/2/2017
8/30/20179/2/2017
8/29/20179/2/2017
8/28/20179/2/2017
8/27/20179/2/2017
8/26/20178/26/2017
8/25/20178/26/2017
8/24/20178/26/2017
8/23/20178/26/2017
8/22/20178/26/2017
8/21/20178/26/2017
8/20/20178/26/2017
8/19/20178/19/2017
8/18/20178/19/2017
8/17/20178/19/2017
8/16/20178/19/2017
8/15/20178/19/2017
8/14/20178/19/2017
8/13/20178/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
DSP
Frequent Visitor

Re: Dynamic week end date

Lots of suggestions here

View solution in original post

Community Support
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)

1.PNG

 

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

View solution in original post

5 REPLIES 5
Super User I
Super User I

Re: Dynamic week end date

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

 

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

Proud to be a Datanaut!
Highlighted
Super User IV
Super User IV

Re: Dynamic week end date

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
joschultz Helper III
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)))))))

 

2017-09-04_9-05-26.jpg

 

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
Frequent Visitor

Re: Dynamic week end date

Lots of suggestions here

View solution in original post

Community Support
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)

1.PNG

 

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

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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