Ignore date from slicer for calculating a measure

[Kandidel]

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]))

[SpartaBI · Accepted solution]

@Kandidel 

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')
    )

[SpartaBI]

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

      

Showcase Report – Contoso By SpartaBI

[Kandidel]

Thanks @SpartaBI , this did not change anything unfortunately.

[SpartaBI]

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

[SpartaBI]

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