Wrong value using RangeSum for aggregated measure

[therealdees]

Hi,

I have this app where we display the "revenue projection" in different sheets and it's calculated with this expression:

Sum({< sale_date -= {"$(=Date(Today()))"}>} total_value)
/
(
Sum(Aggr(NoDistinct Sum({1<target_date = {"<=$(=Today()-1)"}, year = $::year , month= $::month>} day_revenue_target), store_name))
/
Sum(Aggr(NoDistinct Sum({1<month = $::month, year = $::year>} day_revenue_target), store_name))
)

Basically, dividing the "day_revenue_target" by the "month_revenue_target" will give me a % index, and dividing the revenue by this index will give me the revenue projection for the month.

The reason I Aggr by store is because each store will have a different index, and theorically I should sum the result value for each store to find out the company's projection, instead of calculating a single index for the company (we had some issues with the result value before and I came up with this solution and the people I work with have validated the results as correct)

The problem is that I'm trying to use this same expression in a straight table, but I get different values, although it's close to the correct value. 

This table has a "day"  column (data) as a dimension (for comparison with previous years) and a column that will output the projection for the different days. This "day" column is built using dual, the actual value is the day(), that combined with the month and year selected in the filter will correctly dimension the measure columns in the table.

To achieve this, I'm using RangeSum, because the projection must considerate each and every day before the day for that row (e.g  13/11 should considerate the revenue from 01/11 to 13/11, 12/11 should go from 01/11 to 12/11 and so on). The logic kinda follows what I'm trying to do, but the given result is wrong.

I know it's probably quite confusing, but I'm just hopeless. Been trying to fix this for days without success. If anyone could advise me I'd be very thankful. The expression for the RangeSum is the following (although it's not being aggregated by store because I get completely wrong values, this following expression is giving me a quite close result):

(
Sum({<year= $::year, month = $::month>} total_value)
+
RangeSum(Above(
Sum({<year= $::year, month = $::month>} total_value)
, 1, 31))
)
/
(Sum(day_revenue_target) / Sum(Total<sale_date> day_revenue_target)
+
RangeSum(Above(Sum(day_revenue_target) / Sum(Total <sale_date> day_revenue_target), 1, 31)))

[Aasir]

It's challenging to provide an exact solution without a deep understanding of your data model and specific requirements.