Rolling Calculation

[Anonymous]

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é

[v-juanli-msft · Accepted solution]

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]

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