Forum Discussion

oliviar's avatar
oliviar
Data Storage
05-31-2022
Solved

How to Calculate YTD by Month in a pivot table

I have a pivot table that works for my team. It looks like this:

The variance column is a simple formula showing the difference between Budget and Revenue (Revenue - Budget).

I have received a request to expand this pivot (or create a new one based off of the data in this current one) that calculates the YTD for each of these fields. 

So, for example, I'm trying to build 3 new columns in the pivot table:
March Budget YTD = January Budget + February Budget + March Budget
March Revenue YTD = January Revenue + February Revenue + March Revenue
March Variance YTD = March Revenue YTD - March Budget YTD

I need these additional columns for each month, either added to this existing pivot table, or in a new one with a filter for the month the team wants to look at.

Is this possible in a pivot table? I need to keep it split by Customer Type.

How would I solve for this?

 

 

Dashboards & Reporting
Finance Analytics
  • gwolfe's avatar
    gwolfe
    06-01-2022

    Hey oliviar , 

    If you want to show YTD by Month next to your monthly amount you can utilize the "Running Sum" Quick Calculation. If you want to show this next to the monthly amount, just bring in that same value again.

    If you just want to show total for the time period you can use the "Grand Total" on columns

    Hope this helps

2 Replies

Replies have been turned off for this discussion
  • gwolfe's avatar
    gwolfe
    ETL
    06-01-2022

    Hey oliviar , 

    If you want to show YTD by Month next to your monthly amount you can utilize the "Running Sum" Quick Calculation. If you want to show this next to the monthly amount, just bring in that same value again.

    If you just want to show total for the time period you can use the "Grand Total" on columns

    Hope this helps

  • oliviar's avatar
    oliviar
    Data Storage
    07-05-2022

    Hey there,

    Thanks for the feedback. I was aware of the RSUM quick functions, but they weren't working on the data cube my dashboard was connected to. I had to correct a few things.

    Even after my cube was fixed, the Quick Functions still weren't working.

    I found a solution that actually isn't documented anywhere on Sisense webpages or forums that I've come across so far, so I thought I'd share my custom function:

    =RPSUM([Total BUDGET],12)

    This formula takes the monthly budget for the year (as filtered by on my dashboard filter) and cumulatively adds it to the previous month's budget and cumulates from there. At first I only had =RPSUM([Total BUDGET]) and this was working, but only for 2 months at a time (Jan + Feb, reset for March + April, etc). By adding the 12 it knows to repeat after 12 periods, so it takes care of the entire year for me.

    I was able to apply the RPSUM with 12 periods to my Budget, Revenue, and Variance (Revenue - Budget) columns.

    It was a long walk but I got there! I'm grateful to know this function when the Quick Functions don't work.