cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EnrichedUser
Helper II
Helper II

Max Date Minus X Days

Hi,

 

I am hoping this is a fairly easy one and that I am missing something simple.

 

Goal: Evaluate up to the selected date minus 2 days. 

I have two tables, one is a date table "Date" and the other is "SalesOrders"

I have the dax messures:

Picking Backlog =
IF(ISBLANK( [Picks Needed]),
BLANK(),
CALCULATE( [Picks Needed],
FILTER( ALLSELECTED('Date'),
'Date'[Date] <= MAX('Date'[Date]))))

and

Picks Needed =
CALCULATE(COUNT(SalesOrders[Status]), SalesOrders[Status] = "OPEN", SalesOrders[OrderStatus] <> "R")

My goal is use the Picking Backlog Messure but instead of counting to MAX('Date'[Date]), I want to count to the Max Date minus X days. Where X will be 2 for now. 
 
Example:

PickingBacklog
3/2 = 300
3/3 = 340
3/4 = 395
 
Exepected Outcome 
PickingBacklog2days = 300
One Number, Card Visual 
 -----------
Notes

I have tried using the date add function, but the limitation of Max being assest on a column has made it difficult. 
Sample Sales Order Data
BranchID    Date   SalesOrderID     Line Number   Status   Total Price
A3/3S1001Open100
A3/4S1001Open200
B3/3S1021Open300
B3/3S1032Closed400
C3/4S1041Open500
C3/2S1051Closed600
1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @EnrichedUser 

Deduct 2 from the Max Date e.g.

 

Picking Backlog =
IF(ISBLANK( [Picks Needed]),
BLANK(),
CALCULATE( [Picks Needed],
FILTER( ALLSELECTED('Date'),
'Date'[Date] <= (MAX('Date'[Date]) - 2) )))

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
PhilipTreacy
Super User
Super User

Hi @EnrichedUser 

Deduct 2 from the Max Date e.g.

 

Picking Backlog =
IF(ISBLANK( [Picks Needed]),
BLANK(),
CALCULATE( [Picks Needed],
FILTER( ALLSELECTED('Date'),
'Date'[Date] <= (MAX('Date'[Date]) - 2) )))

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


I am sure I tried this and variations of this multiple times before posting this...

thank you

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors