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

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

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.

 

AccountProduct DecriptionCost in $AVG Monthly Cost $Start DateEnd date
AAA12 perftrd$342.7028.55869414/5/20194/4/2020
AAA12 perftrd$1,042.8486.9032136910/6/201910/5/2020
AAA24 perftrd$1,792.50149.3753574/12/20194/11/2020
BBB14 cdt$8,979.92748.327031410/6/201910/5/2020
BBB14 cdt$6,257.03521.419282110/6/201910/5/2020
BBB14 cdt$3,547.53295.62710692/22/20192/21/2020
CC11 tfd$2,342.71195.2254483/21/20193/20/2020
CC11 tfd$1,042.8486.9032136910/6/201910/5/2020
DD23 rt$4,532.80377.73335246/4/20196/4/2020
DD12 rt$8,397.59699.79956296/4/20196/4/2020

 

Table 2

 

4/1/20195/1/20196/1/20197/1/20198/1/20199/1/201910/1/201911/1/201912/1/20191/1/20202/1/20203/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

DateYearQuarter NumQuarter YearQuarter Year OrderMonth NumMonth NameMonth Name Short
Monday, January 1, 201820181Q1 2018201811JanuaryJan
Tuesday, January 2, 201820181Q1 2018201811JanuaryJan
Wednesday, January 3, 201820181Q1 2018201811JanuaryJan
Thursday, January 4, 201820181Q1 2018201811JanuaryJan
Friday, January 5, 201820181Q1 2018201811JanuaryJan
Monday, January 8, 201820181Q1 2018201811JanuaryJan
Tuesday, January 9, 201820181Q1 2018201811JanuaryJan
Wednesday, January 10, 201820181Q1 2018201811JanuaryJan
Thursday, January 11, 201820181Q1 2018201811JanuaryJan
Friday, January 12, 201820181Q1 2018201811JanuaryJan
Monday, January 15, 201820181Q1 2018201811JanuaryJan
 

 

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.
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Rolling 12 months spend with a use of start and end date columns

Check if this can help you

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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: Rolling 12 months spend with a use of start and end date columns

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


Super User III
Super User III

Re: Rolling 12 months spend with a use of start and end date columns

Hi @mbieniasz 

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

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

mbieniasz
Frequent Visitor

Re: Rolling 12 months spend with a use of start and end date columns

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.

Highlighted
Super User IV
Super User IV

Re: Rolling 12 months spend with a use of start and end date columns

Check if this can help you

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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


View solution in original post

Super User III
Super User III

Re: Rolling 12 months spend with a use of start and end date columns

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.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

mbieniasz
Frequent Visitor

Re: Rolling 12 months spend with a use of start and end date columns

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?

 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors