Forum Discussion
EnrichedUser
Helper III
Helper IIIMax 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
and
Picks Needed =
CALCULATE(COUNT(SalesOrders[Status]), SalesOrders[Status] = "OPEN", SalesOrders[OrderStatus] <> "R")
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
PickingBacklog
3/2 = 300
3/3 = 340
3/4 = 395
Exepected Outcome
PickingBacklog2days = 300
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
-----------
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 |
| A | 3/3 | S100 | 1 | Open | 100 |
| A | 3/4 | S100 | 1 | Open | 200 |
| B | 3/3 | S102 | 1 | Open | 300 |
| B | 3/3 | S103 | 2 | Closed | 400 |
| C | 3/4 | S104 | 1 | Open | 500 |
| C | 3/2 | S105 | 1 | Closed | 600 |
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
2 Replies
- PhilipTreacy
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
EnrichedUserI am sure I tried this and variations of this multiple times before posting this...
thank you