cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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 Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! 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 Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! 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))

 

 

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

Imke Feldmann

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.

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 Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! 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.

 

 

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

Imke Feldmann

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors