Date Table Help - Week Ending

[schwinnen]

I am having some trouble understanding how to properly use a date table and I am hoping someone can help me or direct me to a good resource.  

I want to set up a filter so that I can have users choose a week ending based on scheduled delivery date of a shipment.  I have set up the following formula to calculate week ending. 

WEEK ENDING = CALCULATE(Max(QA[DESTINATION SCHEDULED ARRIVAL]), FILTER(QA,QA[TOTAL WEEK # 2] 
= EARLIER( QA[TOTAL WEEK # 2])))

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.

Any help?

[Greg_Deckler · Accepted solution]

Perhaps my Week Ending Quick Measure?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293

[schwinnen]

I'm going to accept that as the solution in case it helps someone else, but this is what I ended up doing.  Please tell me if you see a flaw in my logic.

I used this brilliant formula that you showed me, @Greg_Deckler.

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])
RETURN myNum

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.

[Greg_Deckler]

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.