Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Rolling Calculation

Dear All,

 

I'd like to create a Rolling Forecast table, in which I'd like to show:

  • Current year until the actual month:  Amount from each Group by month (that's not difficult)

  • From actual month until the end of the year: I've Budgets for the Groups, and I like to show these Budgets for each Group by month

 

 

As on the pictures shows (the actual month was April)!

Other problem is that the Budgets for the groups are in other columns (like I have 2018.06 budget column, 2018.07 budget column)

 

Please help me to write a measure which can produce this values of the table!

Thank you in advance for help!

Máté

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Anonymous

    Assume your dataset like this

    Create three new tables (Table2,Table3,Table4)

    Table2 =
VAR may =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget05] ),
        "month", DATE ( 2018, 5, 1 )
    )
VAR june =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget06] ),
        "month", DATE ( 2018, 6, 1 )
    )
VAR july =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget07] ),
        "month", DATE ( 2018, 7, 1 )
    )
VAR aug =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget08] ),
        "month", DATE ( 2018, 8, 1 )
    )
VAR sep =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget09] ),
        "month", DATE ( 2018, 9, 1 )
    )
VAR oct =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget10] ),
        "month", DATE ( 2018, 10, 1 )
    )
VAR nev =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget11] ),
        "month", DATE ( 2018, 11, 1 )
    )
VAR dec =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget12] ),
        "month", DATE ( 2018, 12, 1 )
    )
RETURN
    UNION ( may, june, july, aug, sep, oct, nev, dec )
    Table3 = SUMMARIZE(Table1,[category],[Amount],[actual month value]) 
    Table4 = UNION(Table3,Table2)

    Then in Report view

    Here is my pbix.

     

    Best Regards

    Maggie

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    Hi Anonymous

    Assume your dataset like this

    Create three new tables (Table2,Table3,Table4)

    Table2 =
VAR may =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget05] ),
        "month", DATE ( 2018, 5, 1 )
    )
VAR june =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget06] ),
        "month", DATE ( 2018, 6, 1 )
    )
VAR july =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget07] ),
        "month", DATE ( 2018, 7, 1 )
    )
VAR aug =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget08] ),
        "month", DATE ( 2018, 8, 1 )
    )
VAR sep =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget09] ),
        "month", DATE ( 2018, 9, 1 )
    )
VAR oct =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget10] ),
        "month", DATE ( 2018, 10, 1 )
    )
VAR nev =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget11] ),
        "month", DATE ( 2018, 11, 1 )
    )
VAR dec =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [category], [ budget12] ),
        "month", DATE ( 2018, 12, 1 )
    )
RETURN
    UNION ( may, june, july, aug, sep, oct, nev, dec )
    Table3 = SUMMARIZE(Table1,[category],[Amount],[actual month value]) 
    Table4 = UNION(Table3,Table2)

    Then in Report view

    Here is my pbix.

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi 

       

      I am looking to have the same outcome but from a different structure of datasource. 

       

      I have two tables with same structure (year, month, project, account and value) one table is actuals from the accounting tool and other is the budget from the forecasting tool...

       

      I can union them with an additional attribute that indicates if the row belongs to the actual or budget table.. 

       

      But than I can't understand how to build the visual to only show actuals for the months on past and budget for months in future in the same tabke... The matrix function doesn't work as it shows empty columns for actuals for future dates and side by side comparison of actual and budget för the past months... It has all the data but the view is not what my customer really wants to see...