Forum Discussion
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% 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.
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.
3 Replies
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,
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.
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,
