Forum Discussion
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
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