Forum Discussion

Kpower's avatar
Kpower
Frequent Visitor
9 years ago
Solved

Previous MTD

Hello,

 

the calculation for Prev MTD is like this

Last MTD = CALCULATE(sum(DAILYTRANSACTIONS[Amount]), DATEADD(DATESMTD(YEAR_MONTH_DIMENSION_TAB[Date]), -1, MONTH))

 

The value is showing if only I selected month filter as Feb 2017.

 

And for this

Prev MTD = CALCULATE(DAILYTRANSACTIONS[Amount MTD], SAMEPERIODLASTYEAR(YEAR_MONTH_DIMENSION_TAB[Date]))

It shows Dec 2016 value instead of Jan 2017.

 

Any help would be appreciated.

 

Thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Kpower,

     

    You can refer to below formula to calculate the running total and the previous running total.

     

    Measures:

     

     

    RunningTotal = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))

PreviousRunningTotal = 
var temp=MAX('Table'[Date])
Return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]<=DATE(YEAR(temp),MONTH(temp)-1,DAY(temp))))

     

     

     

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Kpower,

     

    You can refer to below formula to calculate the running total and the previous running total.

     

    Measures:

     

     

    RunningTotal = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))

PreviousRunningTotal = 
var temp=MAX('Table'[Date])
Return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]<=DATE(YEAR(temp),MONTH(temp)-1,DAY(temp))))

     

     

     

    Regards,

    Xiaoxin Sheng