cancel
Showing results for
Did you mean:
Frequent Visitor

## Ignore date from slicer for calculating a measure

Hi all,

need help with a DAX formula, please.

I would like to show sum of sales for selected period plus order book data with delivery year = selected delievery year.

I have no issue with the sum of sales, but with the orderbook data.
For example, when user selects this year (2022), everything is fine.
But when user would like to see, what is already in the order book with delivery year in 2023 for example, I don't get expected result but only zero. Seems, I do not have the filters correct. Could you please help me correct? Thanks in advance.

My formula for order book currently is this:

OB Current Year =
var _lastdate = Calculate(MAX('Orderbook ACT'[Date]), All('Orderbook ACT'))  // find latest order book data
return
CALCULATE(SUM('Orderbook ACT'[Ext Sales]),'Orderbook ACT'[EX/IG] ="EX", filter('Orderbook ACT', 'Orderbook ACT'[Date]=_lastdate),'Orderbook ACT'[Revenue Rec Year] = SELECTEDVALUE('Calendar'[Year]))
1 ACCEPTED SOLUTION
Community Champion
``````OB =
VAR _lastdate =
CALCULATE ( MAX ( 'Orderbook ACT'[Date] ), ALL ( 'Orderbook ACT' ) )
VAR _recognition_year = SELECTEDVALUE ( 'Calendar'[Year] )
RETURN
CALCULATE (
SUM ( 'Orderbook ACT'[Ext Sales] ),
'Orderbook ACT'[EX/IG] = "EX",
'Orderbook ACT'[Date] = _lastdate,
'Orderbook ACT'[Revenue Rec Year] = _recognition_year,
REMOVEFILTERS('Calendar')
)``````
5 REPLIES 5
Community Champion
``````OB =
VAR _lastdate =
CALCULATE ( MAX ( 'Orderbook ACT'[Date] ), ALL ( 'Orderbook ACT' ) )
VAR _recognition_year = SELECTEDVALUE ( 'Calendar'[Year] )
RETURN
CALCULATE (
SUM ( 'Orderbook ACT'[Ext Sales] ),
'Orderbook ACT'[EX/IG] = "EX",
'Orderbook ACT'[Date] = _lastdate,
'Orderbook ACT'[Revenue Rec Year] = _recognition_year,
REMOVEFILTERS('Calendar')
)``````
Community Champion

@Kandidel try:

``````OB Current Year =
var _lastdate = Calculate(MAX('Orderbook ACT'[Date]), All('Orderbook ACT'))  // find latest order book data
return
CALCULATE(SUM('Orderbook ACT'[Ext Sales]),'Orderbook ACT'[EX/IG] ="EX",
'Orderbook ACT'[Date]=_lastdate,'Orderbook ACT'[Revenue Rec Year] = SELECTEDVALUE('Calendar'[Year]))``````

Frequent Visitor

Thanks @SpartaBI , this did not change anything unfortunately.

Community Champion

@Kandidel you can PM me with a link to zoom / teams and we can look together

Community Champion

@Kandidel
Write this:

``````OB =
VAR _lastdate =
CALCULATE ( MAX ( 'Orderbook ACT'[Date] ), ALL ( 'Orderbook ACT' ) )
RETURN
CALCULATE (
SUM ( 'Orderbook ACT'[Ext Sales] ),
'Orderbook ACT'[EX/IG] = "EX",
'Orderbook ACT'[Date] = _lastdate,
'Orderbook ACT'[Revenue Rec Year] = SELECTEDVALUE ( 'Calendar'[Year] )
)``````

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors