Forum Discussion
Multi year budgets v actual
Super UserHi arumel ,
The annual budget can be split into several periods. The question now is what periods (days, months?) it is going to be split into? Also, provide a workable sample data ( not an image) that can be copy pasted into Excel or you can provide a link to an Excel file in the cloud.
Hi, I only want to compare the annual budget to the expenses for that year .. i.e only need to compare the total 2022 budget to the total 2022 expense for a deprtment or sub department (dont need to brekdown to monthly budget details).
Below are the tables copied:
| Deaprtment | Sub Department | Department Code | Year | Budget |
| A | A1 | A1.1 | 2021 | 100 |
| A | A2 | A2.2 | 2021 | 150 |
| A | A3 | A3.3 | 2021 | 200 |
| A | A4 | A4.4 | 2021 | 120 |
| B | B1 | B1.1 | 2021 | 175 |
| B | B2 | B1.2 | 2021 | 180 |
| B | B3 | B1.3 | 2021 | 300 |
| B | B4 | B1.4 | 2021 | 180 |
| A | A1 | A1.1 | 2022 | 160 |
| A | A2 | A2.2 | 2022 | 200 |
| A | A3 | A3.3 | 2022 | 240 |
| A | A4 | A4.4 | 2022 | 100 |
| B | B1 | B1.1 | 2022 | 160 |
| B | B2 | B1.2 | 2022 | 145 |
| B | B3 | B1.3 | 2022 | 300 |
| B | B4 | B1.4 | 2022 | 200 |
| A | A1 | A1.1 | 2023 | 200 |
| A | A2 | A2.2 | 2023 | 260 |
| A | A3 | A3.3 | 2023 | 170 |
| A | A4 | A4.4 | 2023 | 150 |
| B | B1 | B1.1 | 2023 | 220 |
| B | B2 | B1.2 | 2023 | 175 |
| B | B3 | B1.3 | 2023 | 250 |
| B | B4 | B1.4 | 2023 | 220 |
| Trans Date | Order No | Vendor | Currency | Amount | Department Code |
| 1/1/2021 | 1011 | 1 | USD | 25 | A2.2 |
| 1/2/2021 | 1011 | 1 | USD | 41 | B1.1 |
| 5/8/2021 | 5055 | 2 | USD | 74 | A2.2 |
| 9/9/2021 | 9099 | 3 | USD | 12 | A3.3 |
| 4/4/2022 | 4044 | 2 | EUR | 36 | A4.4 |
| 6/9/2022 | 6066 | 1 | EUR | 11 | B1.1 |
| 1/12/2022 | 1211 | 2 | EUR | 36 | B1.2 |
| 1/3/2023 | 1011 | 3 | GBP | 45 | A1.1 |
| 1/4/2023 | 1011 | 4 | GBP | 93 | A2.2 |
| 9/9/2023 | 9099 | 3 | USD | 44 | A3.3 |
Thanks for your help
- danextian
Hi arumel ,
In your Actuals table, add a year column either using M or DAX. Then create a Year dimensions table and a Department dimension table. Relate both dimension tables to the fact tables. Use the dimension columns from the dimensions table in your visuals. Please see attached pbix for details.
