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 = DISTINCTCOUNT('DW FCTHCFAC'[ConsultantId])
Total Consultants Invoiced ParallelPeriod =
CALCULATE([Total Consultants Invoiced],
PARALLELPERIOD('Date'[Data],-1,MONTH))
Total Consultants Invoiced Parallel MOM =
var hcfac = [Total Consultants Invoiced] - [Total Consultants Invoiced ParallelPeriod]
return
if( hcfac >0,CONCATENATE("+",hcfac),hcfac)
------------------------------------------------------------------------------------------------
This is the incorrect measure , i tried a lot of different dax measures but i dont understand why it is not giving the correct values:
RunnTotal Consultants Invoiced Paralell MOM =
CALCULATE([Invoiced Parallel MOM],FILTER(ALL('Date'),'Date'[Data]<=MAX('Date'[Data])))
----------------------------------------------------------------------------------------------------------------- 
Result at the moment - Incorrect Values at RT Delta

In this table i am using this measure Parallel : 

 
Parallel =
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Parallel Table'[Selection] ) = "Monthly Delta" , [Total Consultants Invoiced Parallel MOM],
SELECTEDVALUE ( 'Parallel Table'[Selection] ) = "RT Delta" , [RunnTotal Consultants Invoiced Paralell MOM])
 
As we can see , the table should be like this, it is supposed to be adding the previous values but i do not understant what is wrong :


 

Note : The values of Total Consultants Invoiced Parallel MOM / Monthly Delta are Correct.
The Incorrect values are RunnTotal Consultants Invoiced Paralell MOM / RT Delta.

 

Thanks for your help!

Need Help

3 Replies

  • 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