Forum Discussion

Billy0503's avatar
Billy0503
Frequent Visitor
8 years ago
Solved

Calculate totals between rows

Hello everyone,   I need some urgent help with this one. I need to show the changes of the Total values, between the single forecast dates.      As in the Excel example the forecast date 01-...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Billy0503,

     

    To calculate the changes, you can firstly calculate the values of last month, then it should be easy to get the changes. The formula below is for your reference. :smileyhappy:

    Changes =
VAR currentYear =
    MAX ( 'Table'[Year] )
VAR currentMonth =
    MAX ( 'Table'[Year] )
RETURN
    SUM ( 'Table'[Values] )
        - CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Year] = currentYear
                    && 'Table'[Month]
                    = currentMonth - 1
            )
        )

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi Billy0503,

 

To calculate the changes, you can firstly calculate the values of last month, then it should be easy to get the changes. The formula below is for your reference. :smileyhappy:

Changes =
VAR currentYear =
    MAX ( 'Table'[Year] )
VAR currentMonth =
    MAX ( 'Table'[Year] )
RETURN
    SUM ( 'Table'[Values] )
        - CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Year] = currentYear
                    && 'Table'[Month]
                    = currentMonth - 1
            )
        )

 

Regards

Billy0503's avatar
Billy0503
Frequent Visitor
8 years ago

You are a genius :) Thanks a lot for your help v-ljerr-msft