Calculate and slice Order Backlog

[Joern]

I'm calculating order on hand (order backlog) show the resuslt in the same visual for diffrent departments. It works well if I don't filter the result using 'cDepName' slicer, but as soon as I select a diffrent department the calculation result shows blank.

visual if slicer cDepName = all

visual if slicer cDepName = any other selection

Dax1: Order Income Cumulative =  CALCULATE([Order Income],'date'[Date] <= MAX ('date'[Date]))

Dax2: Turnover Cumulative = CALCULATE([Turnover],'date'[Date] <= MAX ('date'[Date]))

Dax3: Orders on Hand Total = [Order Income Cumulative]-[Turnover Cumulative]

It seems that my slicer breaks my calcualte function, but i'm not sure how to fix it.

Any Ideas?

[Joern]

@Greg_Deckler it's a multi table data model. Order income, Turnover and Department are in diffrent tables.

[Greg_Deckler]

@Joern Maybe try something like this:

Order Income Cumulative =  
  VAR __MaxDate = MAX ('date'[Date])
  VAR __Result = CALCULATE([Order Income], ALL('date'), 'date'[Date] <= MAX ('date'[Date]))
RETURN
  __Result


or

Order Income Cumulative =  
  VAR __MaxDate = MAX ('date'[Date])
  VAR __Result = CALCULATE([Order Income], ALLSELECTED('date'), 'date'[Date] <= MAX ('date'[Date]))
RETURN
  __Result

[Joern]

@Greg_Deckler I tried both your ideas, but result is still the same. However I'm not sure what your 1st variable MaxDate does, as it seems not to be used later on in your DAX?

Order Income Cumulative =  
  VAR __MaxDate = MAX ('date'[Date])
  VAR __Result = CALCULATE([Order Income], ALL('date'), 'date'[Date] <= MAX ('date'[Date]))
RETURN
  __Result

If I filter department inside my calculate function I get the result for each department, but it's not as nice as using the slicer... 😞 I can use it as a workaround, but still prefer to get the slicer working.

OrderIncomeCumulative = CALCULATE([Order Income],('date'[Date] <= MAX ('date'[Date])),Department[cDepName]= "Standard Parts")

[Greg_Deckler]

@Joern Difficult to say. Is this a single table data model? If so you could be running afoul of Auto Exist.