Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
EnrichedUser
Helper III
Helper III

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.