Month on month cumulative % based on min month of selected period

[Gadzphill]

Hello,

First of all, this is my first post on this forum, I want to address a massive thank you to all people providing support on this forum, I use your contributions daily, this is priceless for a newby like me!! 

I am stuck trying to do the following and couldn't find the solution here therefore requesting help 🙂

sum(Cost)/sum(Volume) is my cost per volume per month: [MonthYear]

I want to achieve the below cumulative % variation based on the selected period, min month of the selected period being the "baseline" (0%)

see the two examples below if selecting two different periods.

Any help you could provide would be sooo appreciated!

If selecting Jan to May 2022
	Cost per volume	cumulative % variation
Jan-22	3.2	0%
Feb-22	4.5	41%
Mar-22	6.2	94%
Apr-22	5.4	69%
May-22	6.4	100%
If selecting March to May 2022
	Cost per volume	cumulative % variation
Mar-22	6.2	0%
Apr-22	5.4	-13%
May-22	6.4	3%

[vinieme12]

please post some sample data and the expression you are currently using to calculate cumulative%

[edwin]

to make the expression simpler, i would create two variables:
vMin = date(min([YourDate]))  this gets the lower bound of your date selection
vMax=date(max([YourDate]))

and in your expression (assuming the you have a one to one of date and cost per volume)

=(only({<Date={">=$(vMin)<=$(vMax)"}>}Cost)-only(total {<Date={'$(vDate)'}>}Cost)) / only(total {<Date={'$(vDate)'}>}Cost)

 of course if you have a different expression to calculate the cost , you insert it into the expression above and just modify the set analysis.

[edwin]

if the user selects a date starting beyond 1/1/2022, the variables will determine the starting date and use that as anchor in the expression with set analysis Date={'$(vDate)'}

[edwin]

so applying the expression sum(Cost)/Sum(Volume):

=(sum({<Date={">=$(vMin)<=$(vMax)"}>}Cost)/sum({<Date={">=$(vMin)<=$(vMax)"}>}Volume)-

sum(total {<Date={'$(vDate)'}>}Cost)/sum(total {<Date={'$(vDate)'}>}Volume))/
(sum(total {<Date={'$(vDate)'}>}Cost)/sum(total {<Date={'$(vDate)'}>}Volume))