cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

I have data that looks like this:

 

DepartmentDateCosts
Dept AOct 201710
Dept BOct 201720
Dept BNov 201730
Dept CNov 201740

 

How the data looks in a table:

 

DepartmentOct-17Nov-17Total
Dept A10 10
Dept B203050
Dept C 4040
Total3070100

 

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

 

Outcome sought

image.png

 

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:

 

image.png

 

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? 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

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?

Roll_Costs_Forward_to_Future_Months_and_Years_and_Get_Totals_of_Costs_Rolled_Forward

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Microsoft
Microsoft

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?

Roll_Costs_Forward_to_Future_Months_and_Years_and_Get_Totals_of_Costs_Rolled_Forward

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Super User IV
Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

 

 

 

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors