Rolling total cost/spend with a use of start and end date columns

[Anonymous]

Hi all,

I wonder if you could help me.

I have a data where in each row I have information to a product. See table 1. What I need to create is measure of rolling total cost for each line using the product for period identified between start date and end date. So for example for the first line I need to have a view from April 2019 through to April 2020 that will be showing rolling total cost - like in example in table 2. I have a seperate table with my calendar (table 3) where I create a connection (using End Date from table 1 and Date from calendar table 3) - I need that connection to be able then to report on rolling total by quarter or year..

table 1.

Account	Product Decription	Cost in $	AVG Monthly Cost $	Start Date	End date
AAA	12 perftrd	$342.70	28.5586941	4/5/2019	4/4/2020
AAA	12 perftrd	$1,042.84	86.90321369	10/6/2019	10/5/2020
AAA	24 perftrd	$1,792.50	149.375357	4/12/2019	4/11/2020
BBB	14 cdt	$8,979.92	748.3270314	10/6/2019	10/5/2020
BBB	14 cdt	$6,257.03	521.4192821	10/6/2019	10/5/2020
BBB	14 cdt	$3,547.53	295.6271069	2/22/2019	2/21/2020
CC	11 tfd	$2,342.71	195.225448	3/21/2019	3/20/2020
CC	11 tfd	$1,042.84	86.90321369	10/6/2019	10/5/2020
DD	23 rt	$4,532.80	377.7333524	6/4/2019	6/4/2020
DD	12 rt	$8,397.59	699.7995629	6/4/2019	6/4/2020

Table 2

4/1/2019	5/1/2019	6/1/2019	7/1/2019	8/1/2019	9/1/2019	10/1/2019	11/1/2019	12/1/2019	1/1/2020	2/1/2020	3/1/2020
$     28.56	$     57.12	$     85.68	$  114.23	$  142.79	$  171.35	$   199.91	$   228.47	$   257.03	$  285.59	$  314.15	$  342.70

Table 3

Date	Year	Quarter Num	Quarter Year	Quarter Year Order	Month Num	Month Name	Month Name Short
Monday, January 1, 2018	2018	1	Q1 2018	20181	1	January	Jan
Tuesday, January 2, 2018	2018	1	Q1 2018	20181	1	January	Jan
Wednesday, January 3, 2018	2018	1	Q1 2018	20181	1	January	Jan
Thursday, January 4, 2018	2018	1	Q1 2018	20181	1	January	Jan
Friday, January 5, 2018	2018	1	Q1 2018	20181	1	January	Jan
Monday, January 8, 2018	2018	1	Q1 2018	20181	1	January	Jan
Tuesday, January 9, 2018	2018	1	Q1 2018	20181	1	January	Jan
Wednesday, January 10, 2018	2018	1	Q1 2018	20181	1	January	Jan
Thursday, January 11, 2018	2018	1	Q1 2018	20181	1	January	Jan
Friday, January 12, 2018	2018	1	Q1 2018	20181	1	January	Jan
Monday, January 15, 2018	2018	1	Q1 2018	20181	1	January	Jan

I am having hard time creating these measure. I tried both of the below but it doesnt work:

First try:

Monthly Rolling Total =

SUMX (

GENERATE (

ADDCOLUMNS(

SUMMARIZE (

Data_Source_Table,

Data_Source_Table[Start Date],

Data_Source_Table[End date]

),

"Total Daily Value", CALCULATE ( SUM ( Data_Source_Table[AVG Monthly Cost $] ) )

),

INTERSECT (

DATESBETWEEN ( 'Calendar table'[Date], Data_Source_Table[Start Date], Data_Source_Table[End date] ),

VALUES ( 'Calendar table'[Date] )

)

),

[Total Daily Value]

)

Second try:

AVG Monthly Cost $ running total in Date =

calculate(sum('Data_Source_Table'[AVG Monthly Cost $]),DATESYTD('Calendar table'[Date]))

Both cases are not working.. 

Any help would be greatly appreciated.

[amitchandak]

Not Very clear. But rolling work like this with date calendar. My Calendar Name is Date

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  

[Anonymous]

Thanks but this is not working. 

Maybe I was not precise enough. I do not need to see 12 month rolling total, but rolling total over the selected time period (between start and end dates). So if a cost amount let's say was showing as $100, and the start date was Jan 1 2019 and end date was Oct 10 2019, then I would need a rolling total going from jan 2019 at 10$, then 10$ for Feb, $30 for March, $40 in April etc until $100 in Oct 2019 and here the running total to stop.

And this logic being applied across all products as the start and end dates differ. I have wronlgy written 12 months - please ignore it as some products are live for longer than 12 months, some for shorter. It has to be calculated based on a time period identified in Start Date and End Date columns.

[amitchandak · Accepted solution]

Check if this can help you

https://www.dropbox.com/s/yuv64v0cneseghx/valueSplitbetweenmonths.pbix?dl=0

[Anonymous]

This is pretty good. Quick question on the back of that. Now I have the running cumulative total per month. How easily could I calculate a new Average Monthly Cost out of that measure?

[ImkeF]

Yes, @amitchandak is a good solution.

But you could also adust my solution to dynamically create the individual months in the query editor like so:

List.Transform(
    {0.. Number.Round(Duration.Days( [End date] - [Start Date]) / 30 ) - 1 }, 
    (l) => Date.AddMonths([Start Date], l)
    )

It works with relationships to the Calendar-table.
See attached file.

[ImkeF]

Hi @Anonymous 

I'd strongly recommend to derive a new table from your existing one with 12 months for each row, containing the monthly figures (as an "ordinary" fact table).

That allows you to use standard time patterns and will probably be much faster than a version with measures on your existing table.

In the query editor, reference your table and  add a column with this formula and expand the results:

List.Transform({0..11}, (l) => Date.AddMonths([Start Date], l))