Rolling total for a calculated measure (matrix)

[Anonymous]

Hi,

I have a matrix that shows a variance between my budget and expense, or revenue. There is a measure that calculates the variance % and it works fine. One thing id like to change about it is have it rolling forward instead just that months variance percentage. So for example, if September is 10% spent and then October another 10% spent, October will show 20%. In the total it will show the final total %, but i havent been able to apply conditional formating to the grand totals, and would like to try that out later on maybe.

Here is my current measures code.

% Spent Revenue = IFERROR(CALCULATE(SUM(GLEntry[Revenue]))/CALCULATE(MAX(GLEntry[Revenue Budget])),0)

Here is a sample picture. 

 Sample

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[v-frfei-msft]

Hi @Anonymous,

To update your formula as below.

% Spent Revenue =
IFERROR (
    CALCULATE (
        SUM ( GLEntry[Revenue] ),
        FILTER (
            ALL ( GLEntry ),
            GLEntry[date].[MonthNo] <= MAX ( GLEntry[date].[MonthNo] )
        )
    )
        / CALCULATE ( MAX ( GLEntry[Revenue Budget] ) ),
    0
)

If it doesn't work, kindly share your pbix or smaple data to me. Please upload the file to One Drive and share the link here.

Regards,

Frank

[Anonymous]

@v-frfei-msft

It seemed to work on my revenue but not my expenses, i think that is because my expenses are broken out by department and have sepate budgets, unlike revenue. 

For this example i have two department payrolls in separate matrix, then filtered to only be that certain department. Here is what they look like. I have also added a OneDrive link to the two tables that are used for these matrix. 

Excel file for tables