Create Multiple Cumulative Columns

[swan1099]

Hello,

I've extracted a simple GL Dataset out of my GL (have it set up so I can extract by the month to keep things simple). Within PowerQuery (Get & Transform) I've shaped my data so that it's pivoted by the date (y axis in pic) and GL Account (x axis in pic). Is it possible for me to now put a command into PowerQuery so that it will simply cumulatively total each of the GL columns top to bottom?

In the example attached, the first 5 columns would remain unchanged from 6/1-6/30 as no balances were added to them. Conversly, in the sixth column (headed by GL 21668), it would read 770000 through June 5th, to then it would read 0 to the bottom.

After this my plan is to then unpivot the columns and I will be left with a cumulative total by date & by GL code.

Any help would be much appreciated, thanks!

[Ashish_Mathur]

Hi,

I would suggest that you unpivot your dataseta and have the GL codes in a single column.  So your dataset should be a 3 column one - Date. GL code and Amount.  From this dataset build your visual.

[swan1099]

Hi Ashish,

Agreed on the unpivot, but prior to doing that, I need to get each GL column cumulatively summed downwards. is this possible without too much sophistication?

[Ashish_Mathur]

Don't know.  Sorry.

[Seward12533]

@swan1099have you tried a DAX table solution? I believe this would be the right approach. With the right filters and functions you can do single month, MTD, YTD, CumulativeTD, you may not even need the actual table but if you do SUMMARIZE should do the things trick.

[Seward12533]

Not sure about the M but perhaps an easier way is to use create a Measure that calculated Cumulative totals and then use the Summarize in DAX to build a table using DAX (the New Table) button is on the Modeling TAB) that will build a table by Date and GL codes with the Cumulative totals. After the table is created you can link it into your model as you would any other table.