Month to Date sales - day by day

[M_Zaki]

Dears,

I need to calculate Month to Date sales - day by day, but i get the same value for sales as:

Month/Day	12/1	12/2	12/3	12/4	12/5
Sales	-15,608,809.37	-16,142,777.27	-16,368,495.52	-15,187,632.97	-14,702,194.26
Month to Date Sales	-15,608,809.37	-16,142,777.27	-16,368,495.52	-15,187,632.97	-14,702,194.26

"Sales" is already a measure with sum of total amounts of sales this day.

I need it to be like this:

Month/Day	12/1	12/2	12/3	12/4	12/5
Sales	-15,608,809.37	-16,142,777.27	-16,368,495.52	-15,187,632.97	-14,702,194.26
Month to Date Sales	-15,608,809.37	-31,751,586.64	-48,120,082.16	-63,307,715.13	-78,009,909.39

[sunny_talwar · Accepted solution]

Try this

RangeSum(Above(Sum(Sales), 0, RowNo()))

If this is in a pivot table with Date pivoted across the top... then try this

RangeSum(Before(Sum(Sales), 0, ColumnNo()))

[M_Zaki]

Thanks, it worked well

[M_Zaki]

But i need to reset calculation every start of month, not aggregating forever

[sunny_talwar]

May be this

RangeSum(Above(Sum(Sales), 0, Day(DateField)))

or

Aggr(RangeSum(Above(Sum(Sales), 0, RowNo())), MonthField, DateField)

[M_Zaki]

Not working

Note: this model is what get me results:

RangeSum(Before(Sum(Sales), 0, ColumnNo()))

[sunny_talwar]

Then try this

RangeSum(Before(Sum(Sales), 0, Day(DateField)))

but this should remain the same....

Aggr(RangeSum(Above(Sum(Sales), 0, RowNo())), MonthField, DateField)

[M_Zaki]

Dear Sunny,

both of them not working,

for clarification, your suggestion with:

RangeSum(Before(Sum(Sales), 0, ColumnNo())

 is working fine, but it will sum all values starting from first day in table to the end, even there are days for consecutive months.

i need to reset the sum every start of month.

thanks for your cooperation

[sunny_talwar]

Would you be able to share a sample where I can see the issue?

[M_Zaki]

Many Thanks Sunny, it's working fine now with this:

RangeSum(Before(Sum(Sales), 0, Day(max(DateField))))