Rolling calculation formula help

[Anonymous]

Hi,

I have a matrix that shows budget to actuals than the percentage of that spent. The % spent is measure that is supposed to be a rolling number on to the next month, like if one month is 10% and the next is 15%, it would show 25%.

Here is the code ive been working on, it may be the "Glaccount[Account]" portion, im not necissarily sure what to put there.

% Spent Exp = 
IFERROR (
    CALCULATE (
        SUM ( GLEntry[Expense] ),
        FILTER (
            ALLEXCEPT(GLEntry,Glacount[Account]),
            GLEntry[Entry_date] <= MAX ( GLEntry[Entry_date] )
        )
    )
        / CALCULATE ( MAX( GLEntry[Expense Budget] ) ),
    0
)

This is what the matrix currently looks like.

[v-jiascu-msft]

Hi @Anonymous,

It seems you should try dax/summarize-function-dax in your scenario. It could be like below. If you'd like a precise formula, please provide a sample. Please mask the sensitive parts.

% Spent Exp =
CALCULATE (
    SUMX (
        SUMMARIZE (
            GlEntry,
            [Account],
            [Month],
            "%Spent", DIVIDE ( SUM ( GlEntry[Expense] ), MAX ( GlEntry[Expense Budget] ) )
        ),
        [%Spent]
    ),
    FILTER (
        ALLEXCEPT ( GLEntry, Glacount[Account] ),
        GLEntry[Entry_date] <= MAX ( GLEntry[Entry_date] )
    )
)

Best Regards,

[Anonymous · Accepted solution]

@v-jiascu-msft

I tried the code you gave and it seems to still give me issues. I think it is messing up with the different department budgets, but unsure.

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. 

Tables in Excel file

[v-jiascu-msft]

Hi @Anonymous,

Please make sure your sample doesn't contain anything sensitive.

There are two solutions in the attached demo. Please download it. If you'd like the [solution 2] that needs no new table, please delete the relationship between [Calendar] and [Glentry].

% Spent Exp 2 =
CALCULATE (
    SUMX (
        SUMMARIZE (
            GlEntry,
            Glaccount[Account],
            'Calendar'[Date].[Year],
            'Calendar'[Date].[Month],
            "%Spent", DIVIDE ( SUM ( GlEntry[Expense] ), MAX ( GlEntry[Expense Budget] ) )
        ),
        [%Spent]
    ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

Best Regards,