Forum Discussion

AAMW01's avatar
AAMW01
Icon for Helper I rankHelper I
5 years ago
Solved

Adding Budget Data from excel spreadsheet & displaying it with actual data from database tables

I have a spreadsheet with a list of manual entry budget data which needs to be used by multiple tables from a database. I have a calendar table which links to each database table and the calendar also links to excel spreadsheet budget table. The calendar table acts as a mutual link between them.

Here is an example of the calendar table

 

DateYearMonthDayFiscalYearNumberFiscalYearDisplayFiscalMonthFiscal Month Name
01/05/20212021May12020FY20-2112May
01/06/20212021June12021FY21-221June
01/07/20212021July12021FY21-222July
01/08/20212021August12021FY21-223August
01/09/20212021September12021FY21-224September
01/10/20212021October12021FY21-225October
01/11/20212021November12021FY21-226November
01/12/20212021December12021FY21-227December
01/01/20222021January12021FY21-228January
01/02/20222021February12021FY21-229February
01/03/20222021March12021FY21-2210March


Created by:

Calendar Table = ADDCOLUMNS(
CALENDAR(DATE(2003,01,01), DATE(2025,01,01)),
"Year", YEAR([Date]),
"Month Year Num", CONCATENATE(YEAR([Date]), FORMAT([Date],"MMM")),
"Month", FORMAT([Date], "MMM"),
"DAY", FORMAT([Date], "D"),
)
FiscalYearNumber = If( Month([Date]) >= 6 , Year([Date]),Year([Date]) -1 )
FiscalYearDisplay = "FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2)
Fiscal Month Name = SWITCH('Calendar Table'[FiscalMonth],
1, "June", 2, "July", 3, "August", 4, "September", 5, "October", 6, "November", 7, "December", 8, "January", 9, "February", 10, "March", 11, "April", 12, "May")

I then, for example, have a sales table which shows all transactions.
CustomerAccNoCustomerAccNameTransactionDateValueGroup
1Name 101/01/202150Type 1
2Name 203/02/2021120Type 1
2Name 207/03/202144Type 1
4Name 422/03/2021123Type 2
2Name 223/03/202133Type 1
1Name 110/04/202188Type 1
5Name 511/04/2021180Type 3
6Name 611/04/202132Type 3
5Name 511/04/20215Type 3
This links to the calendar table on date.
Then in my Budget excel spreadsheet I have a column for SalesType1, SalesType2, SalesType3 along with all other columns for all other tables like Overheads based on certain types etc.... The budgets are monthly which also have a link to the calendar table on date

DateBudgetsSalesBudgetsType1SalesBudgetsType2SalesBudgetsType3
01/06/2021225025
01/07/20211511050
01/08/2021257565
01/09/20213012075
01/10/202130115100
01/11/20215110125
01/12/202110100150
01/01/20221590175

The only work around I can think of has been creating a column in the connected table (Calendar Table) and have each column representing the budget column where the group type = the budget group type. Here is an example

SalesBudgetType1 = CALCULATE(SUM(Budgets[SalesBudgetType1]),'Sales'[Group] = "Type 1")
 
I now have a visual  table in power BI using actual figures, it shows for all rows of group types. 

Based on my circumstances can you please assist me on how I have linked the external excel budget data to the actual data tables and if there is a better way to display the results as the column shows incorrect data in rows I don't want, marked as an X. Potentially need an ELSE 0 statement when it doesn't equal the correct Group type? 

 

Any help appreciated.

Need Help
  • AAMW01's avatar
    AAMW01
    5 years ago

    Thank you for your detailed solution. 

     

    I managed to display the budget data as a DAX function in the sales table:

    Sales output forecast = CALCULATE(
    SUM('Budgets'[Value]),
    FILTER('Budgets',
    'Budgets'[GroupType] = MAX('Sales'[Name])
    )
    )
    Not really sure why I needed to use MAX and why I couldn't just use 'Budgets'[GroupType] = 'Sales'[Name]. 
     
    EDIT: see reply from v-kkf-msft who explains why.


    Either way this displays the correct data from the budgets table relating to the group type and date.
     
    I then use date filters to view by certain dates.

10 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    AAMW01 

    Adding KEEPFILTERS to your measure should restrict what lines the budget $ is showing on.

    SalesBudgetType1 =
CALCULATE (
    SUM ( Budgets[SalesBudgetType1] ),
    KEEPFILTERS ( 'Sales'[Group] = "Type 1" )
)

    The other option would be to unpivot the budget data, add the group as a column and create a group table that could link to both your budget and actuals, the same way your date table links to both.

    • AAMW01's avatar
      AAMW01
      Icon for Helper I rankHelper I
      5 years ago

      The KeepFilters function does not change the outcome. The problem I have is that the sales transactions table do not show future dates, only the calendar table does. If I put the calculation in the sales table, it does not show any values. If I put the calculation in the calendar table, it uses seperate names.

      *The title of the first table is the budget data for sales, not actual sales data.

      I have unpivoted the tables and now have Date, Attribute & Value. 

      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        5 years ago

        Now you will need to add a type table to your model that links to both of your fact tables.  Then in your visual pull the type from the type table and it will filter both the sales and budget tables the same way the date table filters both.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    AAMW01 , Unpivot the type column in budget , you will get row values in column say type

    SalesBudgetsType1 , SalesBudgetsType2, SalesBudgetsType3

     

    replace SalesBudgets with empty space and you will get Type1,Type2 and Type3

     

    You can common group table joining both sales and Budget, along with calendar