How to create carry forward totals in a table

[Tascoli-J]

Hello,

I need to create a report that has dates as columns.  At the end of the first column (say 1 Jan) is a total which I want to display at the top of the next column (2 Jan) - a carried forward total.  The total of column 2 Jan will include the carried forward total.  This is repeate in the proceeding columns.

Example:

Date	1 Jan	2 Jan	3 Jan	4 Jan
Carried Forward		140	255	360
ABC Co	110	100	25
XYZ Co		15	70	200
AAA	30		10	50
Total	140	255	360	610

Thanks

[v-zhangti]

Hi, @Tascoli-J 

You can try the following methods.

Column = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Due Date]<EARLIER('Table'[Due Date])))

Table:

New table = 
Var _table1=SUMMARIZE('Table','Table'[Due Date],'Table'[Value],'Table'[Module],"Index","1")
Var _table2=SUMMARIZE('Table','Table'[Due Date],'Table'[Column],"Module","Carried Forward","Index","2")
Return
UNION(_table1,_table2)

Column "Module" sort by Index column.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[SueM]

Thank you for your suggested solution.I have tried it but it is not caculating the values correctly. I am wondering whether it is because on my model, the due date is not consecutive.  It seems the error comes in on the first carried forward amount, but thereafter it seems to work in theory (amounts are incorrect due to the first incorrect carried forward amount). 

[SueM]

Hi there. I have found the issue causing the differences. There was a filter on the visual which was not included in the 'New Table' configuration. The 'Carried Forward' column is now working correctly, thank you! There is just one line which was excluded from the calculation on the 'New table', where there are two rows with the same amount for the same module on the same date, but a different reference number. Only one of them was 'picked up'. Is there a way to include all when summarising?

[SueM]

Hi there. I was able to include the 'duplicate' row I mentioned in my previous message, by including the reference number in the formula as follows:

New table =

Var _table1=SUMMARIZE('Table','Table'[CurrentDates],'Table'[Balance],

'Table'[Module],'Table'[ReferenceNbr],"Index","1")

Var _table2=SUMMARIZE('Table','Table'[CurrentDates],'Table'[Column],

"Module","Carried Forward","ReferenceNbr"," ","Index","2")

Return

UNION(_table1,_table2)

Thank you again for your help!

[punitkhatri]

Hey @Tascoli-J ,
I have used the following measure :- 

CarryForward = CALCULATE(SUM('Sample'[Amount]), FILTER(ALLSELECTED('Sample'), 'Sample'[Date] < MIN('Sample'[Date])))

This is the output that I've got :-

Hope it helps.

[SueM]

Hi there

Thank you for your formula for the carried forward amounts.

I have a slighlty more complicated report where I need the carried forward amount to be added to one of the rows in the matrix.  In the example below, the rows consist of the module (CA being a cash account, in which the amount is 'today's' balance, AP and AR are the open documents amounts for AR and AP going forward). The cash account only has data for the current date, whereas the AR and AP may or may not have data in future dates, depending on the due date of the open documents.

What I need to show is the total cash amount from he first date to be shown in the CA of the next date, and so on.

When using the carried forward formula from this post I am not able to add this to the rows in my Matrix, but only as a value in the matirx. I get something like this:

I hope this is clear and you are able to help.

Thank you.