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...
Need Help
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")