Need help in correcting the formula

[farheenayesha]

Hi,

I need help in calculating an expression called 'Needed AWS to hit the Goal'. Please find the formaula below

[Needed AWS to hit the Goal] = ([Total Goal] - [Running sum of Sales])/[Remaining Weeks]

Total Goal = Sum(Goal)

Running sum of Sales = sum(Sales)      //Suppose if I select Apr in month filter then the sum(sales) should take values from Jan till Apr. I am not able to achieve this. Please help me to achieve this as well. Similarly if i select Mar it should take values from Jan to Mar.

Remaining Weeks = avg([No . of Weeks])  //  Ex. if i select Apr the remaining weeks would be avg of [No . of Weeks] for May.

The graph should show up values as below image.( the below image is from tableau)

I want to display AWS staring from Jan to month selected. Ex. If I select Apr i want to show AWS from Jan to APR and if i select Mar then AWS should show from Jan to MAR only. All other values should be blank as shown in the above picture.

similarly, I want to display [Needed AWS to hit the Goal] as below.

Ex. If I select Apr i want to show [Needed AWS to hit the Goal] from May to Jun.(Forecasted values)   // Note : data is only till June

I already have calculations and graph in place. However i just need your help to correct the formula to get the correct values as below

[sasiparupudi1]

Running Sales expression should be

=sum({<Month={'>=$(=min({1}Month))<=$(=max(Month))'}>}Sales)

[farheenayesha]

Hi Sunny,

Thank you so much for your help. You are super genius. I am not able to reply on your post hence replying from here. Thank you so much once again. I am not able to mark you answer as correct.

Thanks,

Ayeesha

[farheenayesha]

Thank you Sripad for your help. Your expression for running sales works perfect.