Nov 21 2022 12:54 AM
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 team members. However, here is my problem:
On my Master I have two columns;
one is called time period:
A2210 (A for actual)
A2110 (A21 for Last Year)
B2210
A2209 (B for Budget)
A2109
B2209
the other is called Amount
100
93
80
120
110
90
(this is an example)
now, I created two formulas for my Query (I’m very beginner so please don’t judge me :face_with_tears_of_joy:). Expense ES is my table with the costs and, Time Period is my table with the time period (I separated both of them in the formula because I am using a slicer and I needed to create relationships)
Var (-) = Calculate(sum(Expenses ES), time period=A2210) - Calculate (sum(Expense ES), time period = B2210)
Var % = Calculate(sum(Expenses ES), time period=A2210) / Calculate (sum(Expense ES), time period = B2210) -1
here comes the issue: this formula is very static which means that I would have to update by hand each month for ex: now in November I will have to update for 25 workbooks A2211 and B2211.
do you have any idea on how to avoid this, I can’t change the date format (A22 etc… because it comes from a different software…
hope you will be able to help… thank you
Nov 21 2022 06:52 AM
What you need (I think, although it's a bit difficult to picture exactly what you have here without a sample of the actual)... what you need, I think, is to make use of the INDIRECT function, so that you can change a single cell, off to the side, to contain the month. (10 for October, 11 for November, etc). And then INDIRECT uses that to construct the full formulas in each case.
Here's a useful reference for understanding how INDIRECT can be used.
If you want more help, you can help us help you by posting a copy (or a facsimile, with identifiable/confidential/private data removed) of your spreadsheet on GoogleDrive or OneDrive, and posting a link here granting access.
Dec 29 2022 01:13 AM
Dec 29 2022 09:37 AM
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 )