Forum Discussion

arumel's avatar
arumel
Frequent Visitor
2 years ago

Multi year budgets v actual

Hi all, new to power BI and need some help with the intial setup for a dashboard - Essentially this is a budget to expense report.   I have 2 fact tables:   1: Budget = Budget data is on an annua...
Need Help
danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

Hi 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.

  • arumel's avatar
    arumel
    Frequent Visitor
    2 years ago

    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:

     

    DeaprtmentSub DepartmentDepartment CodeYearBudget
    AA1A1.12021100
    AA2A2.22021150
    AA3A3.32021200
    AA4A4.42021120
    BB1B1.12021175
    BB2B1.22021180
    BB3B1.32021300
    BB4B1.42021180
    AA1A1.12022160
    AA2A2.22022200
    AA3A3.32022240
    AA4A4.42022100
    BB1B1.12022160
    BB2B1.22022145
    BB3B1.32022300
    BB4B1.42022200
    AA1A1.12023200
    AA2A2.22023260
    AA3A3.32023170
    AA4A4.42023150
    BB1B1.12023220
    BB2B1.22023175
    BB3B1.32023250
    BB4B1.42023220

     

    Trans DateOrder NoVendorCurrencyAmountDepartment Code
    1/1/202110111USD25A2.2
    1/2/202110111USD41B1.1
    5/8/202150552USD74A2.2
    9/9/202190993USD12A3.3
    4/4/202240442EUR36A4.4
    6/9/202260661EUR11B1.1
    1/12/202212112EUR36B1.2
    1/3/202310113GBP45A1.1
    1/4/202310114GBP93A2.2
    9/9/202390993USD44A3.3

     

    Thanks for your help

    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      2 years ago

      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.

       

      • arumel's avatar
        arumel
        Frequent Visitor
        2 years ago

        Thanks for your help .. im going to have a play with this tonight .. will let you know how i get on

         

        Regards