Hi
@Peter Muller ,
Yes it can be done. I would advice to load all data in the data model.
Normalize your data: 4 companies in a dimension or "lookup" table; same for divisions, transaction types, categories. and fees types.
Then 2 fact tables with budgets and actuals.
And add a calendar table for time intelligence.
Once loaded, the model basically can look something like below. I do suspect the transaction types can be linked to categories, then the model will be slightly different.
It allows to create pivots on multiple table. I also suggest to learn the basics of DAX formulas as well. You can create measures that compare actuals versus budget.
I hope this all helps and gives you a direction how to proceeed.
Book1 |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q |
---|
1 | | | | | | | | | | | | | | | | | |
---|
2 | | | | | | | | | | | | | | | | | |
---|
3 | | Companies | | Transaction Types | | Categories | | | | | | | | | | | |
---|
4 | | A | | Income | | Salaries | | | Column Labels | | | | | | | | |
---|
5 | | B | | Expense | | Maintenance | | | 2021 | | | | | | | | |
---|
6 | | C | | | | Admin | | | januari | | februari | | maart | | april | | |
---|
7 | | D | | | | | | Row Labels | Sum of Actuals | Sum of Budget | Sum of Actuals | Sum of Budget | Sum of Actuals | Sum of Budget | Sum of Actuals | Sum of Budget | |
---|
8 | | | | | | | | Expense | | | | | | | | | |
---|
9 | | | | | | | | Admin | | | | | | | | | |
---|
10 | | Budget | Transaction Types | Companies | Categories | Month | | A | | | | | | 500 | | | |
---|
11 | | 100 | Expense | A | Salaries | 1/01/2021 | | B | 25 | | | | | | | | |
---|
12 | | 500 | Expense | A | Admin | 1/03/2021 | | C | | | | | 25 | | | | |
---|
13 | | 600 | Expense | B | Salaries | 1/01/2021 | | Salaries | | | | | | | | | |
---|
14 | | 1000 | Income | B | Maintenance | 1/04/2021 | | A | | 100 | | | 50 | | | | |
---|
15 | | 750 | Income | C | Maintenance | 1/04/2021 | | B | | 600 | | | 850 | | | | |
---|
16 | | | | | | | | D | | | 10 | | | | | | |
---|
17 | | | | | | | | Income | | | | | | | | | |
---|
18 | | Actuals | Companies | Categories | Date | Transaction Types | | Maintenance | | | | | | | | | |
---|
19 | | 50 | A | Salaries | 29/03/2021 | Expense | | A | | | 400 | | | | | | |
---|
20 | | 50 | B | Salaries | 30/03/2021 | Expense | | B | | | | | | | | 1000 | |
---|
21 | | 25 | C | Admin | 25/03/2021 | Expense | | C | | | | | | | | 750 | |
---|
22 | | 100 | D | Maintenance | 18/02/2021 | Income | | D | | | 100 | | | | | | |
---|
23 | | 400 | A | Maintenance | 15/02/2021 | Income | | Grand Total | 25 | 700 | 510 | | 925 | 500 | | 1750 | |
---|
24 | | 800 | B | Salaries | 26/03/2021 | Expense | | | | | | | | | | | |
---|
25 | | 25 | B | Admin | 13/01/2021 | Expense | | | | | | | | | | | |
---|
26 | | 10 | D | Salaries | 19/02/2021 | Expense | | | | | | | | | | | |
---|
27 | | | | | | | | | | | | | | | | | |
---|
|
---|