Forum Discussion

devesqdeves's avatar
devesqdeves
Helper II
4 years ago

Running Totals Using a Monthly Delta

Hello, i am trying to do a running total measure using another measure, Monthly Delta, but the values are not corret. I will put the information below: Measures : Total Consultants Invoiced = DIST...
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

devesqdeves , try like

 

Total Consultants Invoiced ParallelPeriod =
CALCULATE([Total Consultants Invoiced],
dateadd('Date'[Data],-1,MONTH))

 

 

RunnTotal Consultants Invoiced Paralell MOM =
var _max = MAX('Date'[Data])
var _date = date(year(_max) , month(_max)-1, day(_max))
return
CALCULATE([Invoiced Parallel MOM],FILTER(ALL('Date'),'Date'[Data]<=_date))

  • devesqdeves's avatar
    devesqdeves
    Helper II
    4 years ago

    @amitchandak Hi , thanks for the input , i tried ur measures and it gave this error : 

    MdxScript(Model) (108, 13) Calculation error in measure "DW FCTHCFAC"[RunnTotal Consultants Invoiced Paralell MOM]: An argument of the function 'DATE' has the wrong data type or the result is too large or too small.

     

    What should i do ?

    • devesqdeves's avatar
      devesqdeves
      Helper II
      4 years ago
      I removed the "-1" from the _date var and it stopped giving the error but the values continue incorrect :s 
       
      RunnTotal Consultants Invoiced Paralell MOM =
      var _max = MAX('Date'[Data])
      var _date = date(year(_max) , month(_max) , day(_max))
      return
      CALCULATE([Total Consultants Invoiced Parallel MOM],FILTER(ALL('Date'),'Date'[Data]<=_date))
       
       
      The table presents as this with the measure u wrote minus the "-1"  , not adding the values correctly