cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Date 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.

Promise Date Filters = IF(Orders[Expected Delivery Date]=DATE(1900,01,01),"No Delivery Date Set",
IF(DATEDIFF(Orders[Expected Delivery Date],TODAY(),DAY)<7,"Delivery Expected This Week"))

1 ACCEPTED SOLUTION
Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
8 REPLIES 8

@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()),

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"

Super User

@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()
)

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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

@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!

Super User

@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()
)

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thank you so much. This works!!

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors