cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
schwinnen Member
Member

Date Table Help - Week Ending

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Date Table Help - Week Ending

Perhaps my Week Ending Quick Measure?

 

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

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: Date Table Help - Week Ending

Perhaps my Week Ending Quick Measure?

 

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

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

schwinnen Member
Member

Re: Date Table Help - Week Ending

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.

 

Super User IV
Super User IV

Re: Date Table Help - Week Ending

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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors