Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Rolling calculation formula help

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% a...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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] ) )
)

Rolling-calculation-formula-help

 

Best Regards,