Roll Costs Forward to Future Months and Years and Get Totals of Costs Rolled Forward (Virtual Table)

[CS]

I have data that looks like this:

Department	Date	Costs
Dept A	Oct 2017	10
Dept B	Oct 2017	20
Dept B	Nov 2017	30
Dept C	Nov 2017	40

How the data looks in a table:

Department	Oct-17	Nov-17	Total
Dept A	10		10
Dept B	20	30	50
Dept C		40	40
Total	30	70	100

I want to roll the costs forward to each month, total them by year, and then get a total overall.

Outcome sought

I roll the costs forward to each month with the following formula:

Roll Costs Forward = 
CALCULATE( 
       SUM( OrgData[Costs] ), 
       FILTER( ALL( Dates[Date] ), Dates[Date] <= MAX( Dates[Date] ))
)

The outcome from the formula looks like this:

The totals only reflect the amounts that were rolled forward from the data table, that is, the sum of the Oct and Nov 2017. I need the totals for each year to total the costs rolled forward virtually into each month and the overall total to sum the costs for all years.

How can I create a virtual table with the costs rolled into each month and then use this table to get the month, year, and overall totals? Or do I need to create a physical table with the costs rolled forward into each month? 

[v-jiascu-msft · Accepted solution]

Hi @CS,

We can create a new table that will bring all the data together. Then creating a visual could be easy. You can check it out in this file.

1. A table full of desired months (maybe you had one).

2. A new table with all the data.

Table New =
ADDCOLUMNS (
    CROSSJOIN ( 'Table2', VALUES ( 'Table1'[Department] ) ),
    "Costs New", IF (
        ISBLANK (
            LOOKUPVALUE (
                Table1[Costs],
                'Table1'[Date], [Date],
                Table1[Department], [Department]
            )
        ),
        0,
        LOOKUPVALUE (
            Table1[Costs],
            'Table1'[Date], [Date],
            Table1[Department], [Department]
        )
    )
)

3. Create a measure and a Matrix visual.

Measure =
VAR total =
    CALCULATE (
        SUM ( 'Table New'[Costs New] ),
        FILTER (
            ALL ( 'Table New'[Date] ),
            'Table New'[Date] <= MIN ( 'Table New'[Date] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( 'Table New'[Date] ),
        total,
        SUMX (
            SUMMARIZE (
                'Table New',
                'Table New'[Department],
                'Table New'[Date],
                "RowTotal", CALCULATE (
                    SUM ( 'Table New'[Costs New] ),
                    FILTER (
                        ALL ( 'Table New'[Date] ),
                        'Table New'[Date] <= MIN ( 'Table New'[Date] )
                    )
                )
            ),
            [RowTotal]
        )
    )

But I don't know how to add a yearly summary like yours in a Matrix visual. Can you share with us?

Best Regards,

Dale

[CS]

Thank you, @v-jiascu-msft. This is exactly what I wanted to do. 

I was able to get the total for each year in the matrix by having year and month as separate fields on the matrix. However, that setup does not seem to work with this solution as a way to get totals. When the date field is being used as a date, I don't see an option to get the annual totals. With the date hierarchy, zeros are displayed in the months without underlying (physical) table data. . 

[Greg_Deckler]

Perhaps I am mistaken, but it seems like what you want is a basic Rolling Total. There is a Quick Measure for that, click the little down arrow for the column in your Values area and choose Quick Measure and then Rolling Total.

The measure looks like this:

MyColumn running total in Date = 
CALCULATE(
	SUM('Table'[MyColumn]),
	FILTER(
		ALLSELECTED('Calendar'[Date]),
		ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
	)
)

[CS]

I only have quick measure calculations that seem applicable for a rolling average and a running total. It looks as if I could modify the rolling average code to turn it into a sum, but even as an average, I could not get it to work. Interestingly, I had marked my Dates table as a date table and had to remove that designation to get the quick measure to accept my date. I fared better with the running total and at least got a value in each month, but the totals were still only the amount of the underlying data. 

I tried your formula and it brought the data forward, but the totals only reflected the underlying table data.

The quick measure for the rolling average had code that looked like this in the AVERAGEX function:

CALCULATETABLE( SUMMARIZE( VALUES( 'Dates' ), 'Dates'[Date]), __DATE_PERIOD )

I think I need to create a virtual table with CALCULATETABLE, SUMMARIZE, and maybe ADDCOLUMNS. My attempts so far with those functions have not panned out, but I don't know those table functions all that well. Now would be a good time to learn...

Thanks @Greg_Deckler for your response. I think it is on the right path.

[Greg_Deckler]

@CS- A couple quick questions on this. Is the data you present a good representation of the data that you have to work with? What I mean is that you have no data for the other months but are rather using your Date table to project out future months. 

It's a tricky problem because you I think you actually need to calculate what you want 3 different ways. Once for the inner cells, you have that, a different way for the row totals (almost a last value multiplied by the number of months between then and max) and once for the column totals. Yikes.

[CS]

@Greg_Deckler, yes, you have it right. That is how the data is structured. Two months have data and then the data for those months needs to be projected forward. 

Thanks for the link to your article. I will read it tomorrow. Earlier today I had been reading up on ADDCOLUMNS and SUMMARIZE. I think ADDCOLUMNS is the key and that I might have to create a column for each forward month and calculate the data for the month. Once I have the data in the added columns, then I should be able to get the correct totals...maybe.

Thanks for your interest in and help with this issue. 

[Greg_Deckler]

I'm going to sleep on it. Sometimes answers magically come to me.

[Greg_Deckler]

@CS- I'm taking another look at this. Here is a link that might help you, it is something I wrote on measure totals a long time ago:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

I'm mocking this up to see what I can do with it.