Forum Discussion

Joern's avatar
Joern
Regular Visitor
3 years ago

Calculate and slice Order Backlog

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?
Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

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

  • Joern's avatar
    Joern
    Regular Visitor
    3 years ago

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

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      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's avatar
        Joern
        Regular Visitor
        3 years ago

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