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

Lots of suggestions here

View solution in original post

v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.
GilbertQ
Super User
Super User

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

 

 

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

Lots of suggestions here

CheenuSing
Community Champion
Community Champion

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!

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.