Cumulative/Running Total of payments based on daily updates.

[nmyre]

Hello,

I am unsure if this is possible, but I am trying to make a column or measure that adds itself to the previous days data. The issue comes from the date column getting rewrote each time I load the new days data. I am trying to track totals from each day. I think a visual will help, as I know it sounds confusing. I have 2 tables. One is a date table, one is the Funds table and have a relationship by date, like so:

Below are 3 days (or 3 separate data pulls) of the 'Funds' table on 9 July, 10 July, and 11 July. There are 2 elements (10 and 14) in the [Name] column and each have a [Total Budget]. Then is the [Budget Line] with their [Cost]. I added an [Amount Change] Column to visualize the change happening from day to day. Last is the [Load Date]. On the 9th I load the first table. The morning of the 10th, I load the new data and the [Load Date] column changes from 9 to 10 July. The same happens every day. 

The Second Table is the Date table. It looks like this:

[Todays Date], [Todays_Visual], and [Running Column] are all added functions used to display the date on the home dashboard. The [Cost Running Total] was my attempt, but it only has the total of the day I loaded it. The green [What I want] Column shows the sum of the 'Funds'[Cost] from the corresponding days pull. Is this possible?

If you need clarification please let me know.

-N

[nmyre · Accepted solution]

Reached out to a friend, here is the answer:

"If we are trying to force this into action the answer is in the naming of the files.  When I deal with cross sectional time series data I deal with it by naming the file the as of date that I pulled it.  Something as simple as all of the files will end with _20200723.  Then in the transformation of from file from folder in the M before you expand the content you create an M that extracts that section, transforms it to a date, then expands it along with the rest of the files.  Something like this:  IF it doesn’t make sense let me know.  But you are basically pulling out the date from the delimiter (farthest right if could be multiple) and then using a function on the content to expand that with all of the details in the files within the folder.  This is a video I googled just now to see it…as I can’t actually get to youtube not sure the quality but It appears to show the functions of it.  If needed let me know and I will throw together a quick video and post it out there for you Example Here "

Here is the sample file and the second section is the beginning of the M

let

    Source = Folder.Files(FILEPATH&"\Data\DIMES_DCMS_I_001"),

    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Name", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Name.1", "Name.2"}),

    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Name.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Name.2.1", "Name.2.2"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Name.2.1", type date}}),

    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name.2.1", "Content"}),

    Navigation1 = #"Removed Other Columns"{0}[Content]

in

    Navigation1

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

let

    Source = Folder.Files(FILEPATH&"\Data\DIMES_DCMS_I_001"),

    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Name", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Name.1", "Name.2"}),

    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Name.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Name.2.1", "Name.2.2"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Name.2.1", type date}}),

    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name.2.1", "Content"}),

    #"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns", "Transform File (2)", each #"Transform File (2)"([Content])),

    #"Removed Other Columns1" = Table.SelectColumns(#"Invoked Custom Function",{"Name.2.1", "Transform File (2)"}),

    #"Expanded Transform File (2)" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40"}),

    #"Removed Top Rows" = Table.Skip(#"Expanded Transform File (2)",2),

    #"Added Conditional Column" = Table.AddColumn(#"Removed Top Rows", "Custom", each if [Column1] = "Document_Id" then "REPORT DATE" else [Name.2.1]),

    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Name.2.1"}),

    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),

[amitchandak]

@nmyre , what is running total formula you used.

Can you share sample data and sample output in a table format?

have you tried creating a running total from the quick measure?

[nmyre]

@amitchandak 

Here is the formula for the running total formula on the Cost Running Column.

I also have a 'Cost Running Total' measure that I have tried. Here it is:

I can share some real sample data if you wish, but I fear it will just make things confusing as the data tables have over 30 columns with elements that likely doesn't help and thousands of lines of data. I can share the simplified tables though:

Name	Total Budget	Budget Line	Cost (K)	Amt Change	Load Date
14	$          1,000.00	1	$       5.00	0	7/9/2020
14	$          1,000.00	2	$     10.00	0	7/9/2020
14	$          1,000.00	3	$     15.00	0	7/9/2020
14	$          1,000.00	4	$     20.00	0	7/9/2020
14	$          1,000.00	5	$     10.00	0	7/9/2020
10	$             550.00	1	$       5.00	0	7/9/2020
10	$             550.00	2	$       5.00	0	7/9/2020

Name	Total Budget	Budget Line	Cost (K)	Amt Change	Load Date
14	$     1,000.00	1	$     7.00	$     2.00	7/10/2020
14	$     1,000.00	2	$   12.00	$     2.00	7/10/2020
14	$     1,000.00	3	$     5.00	$ (10.00)	7/10/2020
14	$     1,000.00	4	$   30.00	$   10.00	7/10/2020
14	$     1,000.00	5	$   10.00	$          -	7/10/2020
10	$        550.00	1	$     8.00	$     3.00	7/10/2020
10	$        550.00	2	$     5.00	$          -	7/10/2020

Here is an example of the output I am looking for. It is from a different model so the numbers are off.

Here the total builds off of each day, like a running total. The difference is, most running totals have something like "Sales Date" or a date column that is linked to the running total in the data table itself. My data table refreshes everyday, so instead of having a "Sales Date" it has the date I loaded the data. The result is this:

 

I hope that helps.