Forum Discussion
Update of measures Excel Power Pivot
Hi, I created a database with lots of data but to be able to send it to each team members (I did not want them to have access to others’ data), I had to split the master workbook by name of the ...
Hi Mathetes,
My table would be as follow. As you can see I have different time period in the same column, different amount depending on the account and account name. That’s why I find it very hard to find a code that would not require to update each month the formulas.
As stated earlier, now I have to use this formula all the time for variance:
Calculate(sum(Expenses[amount], time period =“A2201”) - calculate(sum(Expenses[amount], time period =“B2201”)
And for the % I had to create two different formula which also complicate my formulas
On that divides Actual and Budget and the other one that gives the variation:
Div = Calculate(sum(Expenses[amount], time period =“A2201”)/calculate(sum(Expenses[amount], time period =“B2201”)
And, on another formula
[Div] - 1
TIME PERIÓD AMOUNT ACCOUNT ACCOUNT NAME
A2101 100 PAYROLL WAGES
B2101 250 PAYROLL WAGES
A2201 200 PAYROLL WAGES
B2203 100 RENT MADRID
A2203 430 RENT MADRID
A2103 608 RENT MADRID
A2101 80 PAYROLL PART TIME
B2101 70 PAYROLL PART TIME
A2201 45 PAYROLL PART TIME
B2203 80 RENT BCN
A2203 90 RENT BCN
A2103 56 RENT BCN
Thank you in advance
My table would be as follow. As you can see I have different time period in the same column, different amount depending on the account and account name. That’s why I find it very hard to find a code that would not require to update each month the formulas.
As stated earlier, now I have to use this formula all the time for variance:
Calculate(sum(Expenses[amount], time period =“A2201”) - calculate(sum(Expenses[amount], time period =“B2201”)
And for the % I had to create two different formula which also complicate my formulas
On that divides Actual and Budget and the other one that gives the variation:
Div = Calculate(sum(Expenses[amount], time period =“A2201”)/calculate(sum(Expenses[amount], time period =“B2201”)
And, on another formula
[Div] - 1
TIME PERIÓD AMOUNT ACCOUNT ACCOUNT NAME
A2101 100 PAYROLL WAGES
B2101 250 PAYROLL WAGES
A2201 200 PAYROLL WAGES
B2203 100 RENT MADRID
A2203 430 RENT MADRID
A2103 608 RENT MADRID
A2101 80 PAYROLL PART TIME
B2101 70 PAYROLL PART TIME
A2201 45 PAYROLL PART TIME
B2203 80 RENT BCN
A2203 90 RENT BCN
A2103 56 RENT BCN
Thank you in advance
For the current date you may use in parameter
"A" & FORMAT(TODAY(), "yymm")
Or load into the model table "par" with the column "Month" and only one value which is text like "2211". When
"A" & VALUES(par[Month])
Something like
= VAR periodA = "A" & VALUES( par[Month] )
VAR periodB = "B" & VALUES( par[Month] )
VAR sumA = CALCULATE( SUM(Expenses[amount], time period =periodA)
VAR sumB = CALCULATE( SUM(Expenses[amount], time period =periodB)
RETURN
DIVIDE( sumA, sumB, 0 )