For the most part this formula works, but it is looking for the max value in DESTINATION SCHEDULED ARRIVAL. If, as is the case sometimes in my data, the latest DESTINATION SCHEDULED ARRIVAL of a particular week is not the last day of the week (Saturday), I end up with week ending date's that are not always a week apart. Now, for instance, the latest DESTINATION SCHEDULED ARRIVAL date in my data is 2/19/19. So I get a week ending 2/16/19 and then a week ending 2/19/19.
I have attached my Date Table. I created a relationship between DESTINATION SCHEDULED ARRIVAL and date in the date table, but I'm not sure where to go from there. I want to use the Week Ending from the Date Table because that would give me the actual date of the week ending for each week. However, I need this to be tied to DESTINATION SCHEDULED ARRIVAL. Other than creating that relationship, I am not sure what to do.
TW # =
VAR MaxWeeks = SUMMARIZE(ALL('Calendar'),'Calendar'[YR],"MaxWeek",MAX('Calendar'[WeekNum]))
VAR MyYear = [YR]
VAR MyStart = SUMX(FILTER(MaxWeeks,[YR]<MyYear),[MaxWeek])
VAR firstYear = CALCULATE(FIRSTNONBLANK('Calendar'[YR],1),ALL('Calendar'))
VAR myNum = IF(MyYear=firstYear,[WeekNum],MyStart+[WeekNum])
Then I used this formula for my Week End Date on my Date (Calendar) table:
Week End Date = CALCULATE(Max('Calendar'[Date]), FILTER('Calendar','Calendar'[TW #] = EARLIER( 'Calendar'[TW #])))
Then I did a lookup using the formula below to get the week ending date. The Date for Relationship field is just a copy of DESTINATION SCHEDULED ARRIVAL with a DATE format instead of a DATE/TIME format.
WE = LOOKUPVALUE('Calendar'[Week End Date],'Calendar'[Date],QA[Date for Relationship])
Again, this looks like it works, but if you see a flaw, let me know. I've done things before that seem correct, but break down the road, leading to more work.
Looks pretty good. I know that when I created that Quick Measure that I ran into issues with boundary cases (weeks that spanned years, end of my date table, etc.) so I would take a hard look at those specific cases.