Skip to main content
cancel
Showing results for 
Search instead 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

Reply
JenWilson
Advocate I
Advocate I

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"))
 
Thanks for your help! 
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
JenWilson
Advocate I
Advocate I

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

 

JenWilson
Advocate I
Advocate I

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"

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

Follow on LinkedIn
@ 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!:
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

 

JenWilson
Advocate I
Advocate I

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

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

Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
JenWilson
Advocate I
Advocate I

@Greg_Deckler Thank you so much. This works!! 

 

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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