Forum Discussion
Cumulative Recurring SUM per Sector
Hi,
Do you want something like this. You may download my file from here. I am accumulating savings from the start of the Year (January 1), rather than the start of the period for which data is available.
When I tried to use Dates from the 'Dates' table, it generates an error.
What was the error? Was it related to the bi-drectional relationship between the Dates and the Operational table? I'm not sure why you'd do that. Normally I'd have a 1 to many relationship from Dates to a fact/transaction table.
Any idea how to make it the reccuring saving appear for every month that is between [Start Date] and [End Date]?
You can't do a between join using relationships in a tabular model. But you can achieve the same effect by not having a join between your Date and Operational tables and doing the "between join" logic in a measure.
I used the following 2 measures to achieve the output above.
Amt = sumx( filter(sales, Sales[StartDate] < Max('Date'[Date]) && Sales[EndDate] > min('Date'[Date]) ) , Sales[Amount])Cummulative Amt = CALCULATE( [Amt] , FILTER(ALL('date'), 'Date'[Date] <= max('Date'[Date])) )You can download a copy of this model from here
Thank you very much to both of you for your help!
Ashish_Mathur's solution ended working for me. d_gosbell The difference between both of your solutions was that Ashish's was cumulative per month. My graph now looks like Ashish's graph in his previous response.
Thanks again to both of you :)
Ben
Ashish_Mathur's solution ended working for me. d_gosbell The difference between both of your solutions was that Ashish's was cumulative per month. My graph now looks like Ashish's graph in his previous response.
I think the main difference in our approaches is that I've done a "Lifetime to Date" while Ashish has done a "Year to Date" (so it resets on the first month of the year). Both are valid depending on the requirements
Hi,
Just reviewed both of your solutions more in details. Both actually hepled as I also used d_gosbell to not keep a YTD limit.
I have also ended up using his formulas as the SUMX seemed to represent more the actual recurring savings per month.
Ashish's solution helped me set one line per month in between start date and end date.
Thanks and cheers,
BenThat's great news Anonymous thanks for letting us know. I think that shows the true power of forums like this - when you can pick up pointers from a couple of different responses and combine them into something that fits your requirements. :)