Forum Discussion
Helper IIDate Filters including 01/01/1900
I have a Orders table. It includes an Expected Delivery Date. This date field is manually filled in as these dates become known therefore there are many occurrances of the date 1/1/1900.
The Expected Delivery Date is part of the table visual of the report. I want the user to be able to filter on the Expected Delivery Date column for occurrances of 1) dates that fall in the current week, and 2) dates have not yet been set (1/1/1900).
I am almost there using the formula below, however the 2nd line (to capture the "Current Week" is set up to just look back 7 days from today. I need help with fixing that portion of the formula so that it looks at only dates that are in the week the report is viewed.
JenWilson Maybe:
Promise Date Filters = IF( Orders[Expected Delivery Date]=DATE(1900,01,01), "No Delivery Date Set", IF( YEAR(Orders[Expected Delivery Date])*100+ WEEKNUM(Orders[Expected Delivery Date]) = YEAR(TODAY()) * 100 + WEEKNUM(TODAY()), "Delivery Expected This Week" ) )Also, you might want to use SWITCH(TRUE(), ...) in order to avoid nested IF statements.
8 Replies
JenWilsonGreg_Deckler Thank you so much. This works!!
- Greg_Deckler
Community Champion
JenWilson Maybe:
Promise Date Filters = IF( Orders[Expected Delivery Date]=DATE(1900,01,01), "No Delivery Date Set", IF( YEAR(Orders[Expected Delivery Date])*100+ WEEKNUM(Orders[Expected Delivery Date]) = YEAR(TODAY()) * 100 + WEEKNUM(TODAY()), "Delivery Expected This Week" ) )Also, you might want to use SWITCH(TRUE(), ...) in order to avoid nested IF statements.
- JenWilson
Greg_Deckler - I need to expand on the above formula to also include "Delivery Expected Last Week". How can I add that?
Also, I am curious as to how this formula would look with SWITCH.Thank you!
- Greg_Deckler
JenWilson I'm not clear on what you mean by including Delivery Expected Last Week. Can you clarify? Here is what it would look like as SWITCH:
Promise Date Filters = SWITCH( TRUE(), Orders[Expected Delivery Date]=DATE(1900,01,01), "No Delivery Date Set", YEAR(Orders[Expected Delivery Date])*100+ WEEKNUM(Orders[Expected Delivery Date]) = YEAR(TODAY()) * 100 + WEEKNUM(TODAY()), "Delivery Expected This Week", BLANK() )
- JenWilson
Hi, Greg
I want to look back at orders that were expected Last Week (as a seperate filter). I am guessing that I just need to add another statement that resembles what you had written for "Delivery Expected This Week" (see below). But instead of looking for dates that are out in the future (next week) I would be looking for dates in the past that fall during the last week.
YEAR(Orders[Expected Delivery Date])*100+ WEEKNUM(Orders[Expected Delivery Date]) =
YEAR(TODAY()) * 100 + WEEKNUM(TODAY()), "Delivery Expected This Week"- Greg_Deckler
JenWilson OK, try this:
Promise Date Filters = SWITCH( TRUE(), Orders[Expected Delivery Date]=DATE(1900,01,01), "No Delivery Date Set", YEAR(Orders[Expected Delivery Date])*100+ WEEKNUM(Orders[Expected Delivery Date]) = YEAR(TODAY()) * 100 + WEEKNUM(TODAY()), "Delivery Expected This Week", YEAR(Orders[Expected Delivery Date])*100+ WEEKNUM(Orders[Expected Delivery Date]) = YEAR(TODAY()) * 100 + WEEKNUM(TODAY())-1, "Delivery Expected Last Week", BLANK() )- JenWilson
Greg_Deckler , I need to add another filter option that would label any dates farther past the "Delivery Expected Last Week". So I have- YEAR(TODAY())*100 + WEEKNUM(TODAY())-1 to identify Delivery dates from last week, but how would I write an additional identifier for dates prior to "Last Week"?
Also, can you please try to explain how this forumula actually works?YEAR(TODAY())*100 + WEEKNUM(TODAY())-1
Thank you!
Jen
- JenWilson
Greg_Deckler that works!
But can you help me understand what each of the weekly statements are asking for (1 pasted below). I am expecially confused by the *100+ WEEKNUM. Thanks!
YEAR(Orders[Expected Delivery Date])*100+ WEEKNUM(Orders[Expected Delivery Date]) = YEAR(TODAY()) * 100 + WEEKNUM(TODAY()),