I need togenerate areport where for any monthin whichyou select,you can seea column to thesales valueof the month,andin another columnthecumulative salesvaluefrom thefirst recordfor that product exists.
Regardless ofwhichmonth ormonthsselect, cumulativecolumnalwaysshows methe total tothat date.
Example: this is the input table,
Product
Month
Sales
Accumulated
A
ago-12
$ 300,00
$ 300,00
A
sep-12
$ 620,00
$ 920,00
A
oct-12
$ 1.900,00
$ 2.820,00
A
nov-12
$ 300,00
$ 3.120,00
A
dic-12
$ 200,00
$ 3.320,00
A
ene-13
$ 140,00
$ 3.460,00
A
feb-13
$ 500,00
$ 3.960,00
A
mar-13
$ 300,00
$ 4.260,00
A
abr-13
$ 300,00
$ 4.560,00
B
may-12
$ 50,00
$ 50,00
B
jun-12
$ 600,00
$ 650,00
B
jul-12
$ 350,00
$ 1.000,00
B
ago-12
$ 200,00
$ 1.200,00
B
sep-12
$ 140,00
$ 1.340,00
B
oct-12
$ 60,00
$ 1.400,00
B
nov-12
$ 50,00
$ 1.450,00
B
dic-12
$ 40,00
$ 1.490,00
B
ene-13
$ 70,00
$ 1.560,00
B
feb-13
$ 100,00
$ 1.660,00
B
mar-13
$ 200,00
$ 1.860,00
B
abr-13
$ 40,00
$ 1.900,00
C
oct-12
$ 50,00
$ 50,00
C
nov-12
$ 60,00
$ 110,00
C
dic-12
$ 240,00
$ 350,00
C
ene-13
$ 120,00
$ 470,00
C
feb-13
$ 100,00
$ 570,00
C
mar-13
$ 200,00
$ 770,00
C
abr-13
$ 60,00
$ 830,00
If I select "abr-2013", I must see:
Product
Month
Sales
Accumulated
A
abr-13
$ 300,00
$ 4.560,00
B
abr-13
$ 40,00
$ 1.900,00
C
abr-13
$ 60,00
$ 830,00
If I select "feb-2013" and "mar-2013", I must see:
Product
Month
Sales
Accumulated
A
feb-13
$ 500,00
$ 3.960,00
A
mar-13
$ 300,00
$ 4.260,00
B
feb-13
$ 100,00
$ 1.660,00
B
mar-13
$ 200,00
$ 1.860,00
C
feb-13
$ 100,00
$ 570,00
C
mar-13
$ 200,00
$ 770,00
Always, "Accumulated" column must display the cumulative sales value from the first date when this product has a row with sales registered.
This case is not YTD analysis, because I need display always from the first date, regardless if means a month ago, a year or a several years, for each product is different.
I try different workarounds for the "Accumulated" expresion, (using Set Analysis, Min(Date), TOTAL, etc), but I can't solve it.