locked
how to build semantic model table from excel 2013 budget table? RRS feed

  • Question

  • I have an excel 2013 budget table produced by finance department. The title of the table is <<Year 2015 Resource Budget>> with columns such as (Item, Budget). There is line of business app which tracks daily usage. It is SQL database which I can access. The SQL table has columns such as Date, Item, Usage. I am asked to produce some PowerView reports using DAX.

    I am thinking about using Excel 2013 PowerPivot. The following is the step of my plan.

    1. Add excel 2013 Budget table to PowerPivot
    2. Transform the budget table using DAX to a new table with includes date. For each item budget, the value can be evenly divided by 365. I don't know if this is possible with DAX and need advice from you
    3. Import daily usage from SQL database into PowerPivot
    4. Merge the imported SQL daily usage table with the transformed budget table with daily breakdown.  The output table has 4 columns (Date, Item, ValueType, Value). The ValueType can be either Budget and Usage. (I also do not know if it is possible to use DAX to merge two PowerPivot table into anther table with added new columns. If it is possible, I would love to hear from you on how to do this)
    5. Finally, I can produce BI reports based on the newly merged table

    Update: Here are more sample data.

    Input Budget

    <<Year 2014 Resource Budget>>
    Item Budge
    Pen 365
    Paper 1095
    <<Year 2015 Resource Budget>>
    Item Budge
    Pen 1825
    Paper 1095
    Bag 365

    I am thinking to expand the Budget as follow but I don't how to use DAX to do it

    Date Item Budge
    31/12/2014 Pen 1
    31/12/2014 Paper 3
    1/01/2015 Pen 5
    1/01/2015 Paper 3
    1/01/2015 Bag 1

    Here is the Daily Usage table

    Date Item Usage
    31/12/2014 Paper 2
    31/12/2014 Pen 6
    1/01/2015 Paper 10
    1/01/2015 Pen 2
    1/01/2015 Bag 1

    I was thinking to merge the expanded Daily Budget and Daily Usage as follow for BI reporting. This was my thinking, may not a good solution using DAX

    Date Item Type Value
    31/12/2014 Paper Usage 2
    31/12/2014 Pen Usage 6
    1/01/2015 Paper Usage 10
    1/01/2015 Pen Usage 2
    1/01/2015 Bag Usage 1
    31/12/2014 Pen Budget 1
    31/12/2014 Paper Budget 3
    1/01/2015 Pen Budget 5
    1/01/2015 Paper Budget 3
    1/01/2015 Bag Budget 1

    Off course, I need to build dimension tables for Date, Item, Item Category, and Value Type

     


    jl


    • Edited by Joe Lee Tuesday, January 13, 2015 12:11 AM
    Monday, January 12, 2015 4:44 AM

Answers

  • Hey, maybe this http://www.daxpatterns.com/budget-patterns/ and this http://www.daxpatterns.com/handling-different-granularities/ gives you some ideas how to combine Actual Data (more "dimensions" and more "granular") and Budget Data. And as Daniel I would keep the actual and the budget data separate
    • Edited by tom.martensMVP Monday, January 12, 2015 9:14 AM
    • Proposed as answer by Michael Amadi Monday, January 12, 2015 9:15 AM
    • Marked as answer by Joe Lee Tuesday, January 13, 2015 11:55 PM
    Monday, January 12, 2015 9:12 AM
  • Hi JL,

    Just for consideration, I'd:

    1. Keep budget and actuals separately - create Fact_Y2015Budget and Fact_Actuals;
    2. Hook both tables to same dimesnions (time; if there is no time for budget, assign it all to the first day of your 2015; line item etc. - whatever you are asked to report about)
    3. If you can, pull usage not only for 2015, but for prior year(s) too
    4. Create measures like [Usage Budget Atteinment YTD]:=TotalYTD(SUM([Usage]),your_time)/([Budget]*PriorYear(TotaYTD(SUM([Usage]),you_time)/CALCULATE(SUM([Usage]),DATESBETWEEN(your_first_day_of_year2015,your_last_day_of_2015))))

    The calculation in #4 compares actual ("usage") to portion of budget that would have been used up if 2015 had the same seasonality as 2014... If you couldmshare specific sample, I'd buuild exact formula...

    Daniel

    • Proposed as answer by Michael Amadi Monday, January 12, 2015 9:15 AM
    • Marked as answer by Joe Lee Tuesday, January 13, 2015 11:56 PM
    Monday, January 12, 2015 8:52 AM

All replies

  • Hi JL,

    Just for consideration, I'd:

    1. Keep budget and actuals separately - create Fact_Y2015Budget and Fact_Actuals;
    2. Hook both tables to same dimesnions (time; if there is no time for budget, assign it all to the first day of your 2015; line item etc. - whatever you are asked to report about)
    3. If you can, pull usage not only for 2015, but for prior year(s) too
    4. Create measures like [Usage Budget Atteinment YTD]:=TotalYTD(SUM([Usage]),your_time)/([Budget]*PriorYear(TotaYTD(SUM([Usage]),you_time)/CALCULATE(SUM([Usage]),DATESBETWEEN(your_first_day_of_year2015,your_last_day_of_2015))))

    The calculation in #4 compares actual ("usage") to portion of budget that would have been used up if 2015 had the same seasonality as 2014... If you couldmshare specific sample, I'd buuild exact formula...

    Daniel

    • Proposed as answer by Michael Amadi Monday, January 12, 2015 9:15 AM
    • Marked as answer by Joe Lee Tuesday, January 13, 2015 11:56 PM
    Monday, January 12, 2015 8:52 AM
  • Hey, maybe this http://www.daxpatterns.com/budget-patterns/ and this http://www.daxpatterns.com/handling-different-granularities/ gives you some ideas how to combine Actual Data (more "dimensions" and more "granular") and Budget Data. And as Daniel I would keep the actual and the budget data separate
    • Edited by tom.martensMVP Monday, January 12, 2015 9:14 AM
    • Proposed as answer by Michael Amadi Monday, January 12, 2015 9:15 AM
    • Marked as answer by Joe Lee Tuesday, January 13, 2015 11:55 PM
    Monday, January 12, 2015 9:12 AM
  • Hi Daniel

    I really like your idea but I do not how to do it. It would nice if you could give details/steps/links on how to achieve this

    I also updated the issue with more sample data for your reference


    jl

    Tuesday, January 13, 2015 12:14 AM