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
schwinnen
Helper V
Helper V

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
Greg_Deckler
Super User
Super User

Perhaps my Week Ending Quick Measure?

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Perhaps my Week Ending Quick Measure?

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.