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

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