cumulative table with sum cash applied versus total invoiced$ per month

[ugurarslan]

Dear all,

I have table with the following dimension and measurement:

Dimension:

day([Month date])

Measurement:

(Sum({<[Year Month]={'$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])

/

Sum({<[Year Month]={'$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))

Table now shows:

Days is created by using dimension Day([month date])

Measurement by using:

(Sum({<[Year Month]={'$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])

/

Sum({<[Year Month]={'$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))

Days	Measurement
1	6
2	3
3	5
4	6
5	8
6	8
7	4
8	3
9	6
10	7
11	8
12	8
13	8
14	8
15	9
16	9
17	6
18	4
19	3
20	2

Now I want to see this result as cumulative.

Day	Cumulative
1	6
2	9
3	14
4	20
5	28
6	36
7	40
8	43
9	49
10	56
11	64
12	72
13	80
14	88
15	97
16	106
17	112
18	116
19	119
20	121

How do I need to do this?

Please help me.

Ugur

[dplr-rn]

Try range sum with above e.g.

rangesum(above(Sum(LineSalesAmount),0,RowNo()))

[justinphamvn]

Hi Ugur,

You can try this:

1. Create a variable v_Measurement

(Sum({<[Year Month]={'$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])
/
Sum({<[Year Month]={'$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))

2. Using the RANGESUM function in combination with the ABOVE function to accumulate the values you need.

Create Measure Accumulate:

rangesum( above( $(v_Measurement),0,rowno()))

3. Create Bar Chart or Table with

Dimension: day([Month date])

Measure: Accumulate

Hope this helps,

Justin.

[ugurarslan]

Dear Justin,

Thank you very much for your reply, appreciate it. Unfortunately the outcome is '-'.

Can you please write the exact formula with using my example?

Thanks

Ugur

[ugurarslan]

Dear Dilip.

Thank you very much for your answer,

Would it possible for you to write the formula with using my example?

Thanks

Ugur

[justinphamvn]

Hi Ugur,

Pls try this:

rangesum( above( (Sum({<[Year Month]={'$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])
/
Sum({<[Year Month]={'$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))
,0,rowno())) 

I think If your current Measure is working so the Measure Accumulate is working too

p/s: The Measure Accumulate like this: rangesum( above( sum([Expression]),0,rowno()))

Thanks,

Justin.

[dplr-rn]

just replace the sum function by yours

rangesum(above(

(Sum({<[Year Month]={'$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])

/

Sum({<[Year Month]={'$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))

, 0,RowNo() ) )

[agigliotti]

Let's try replacing RowNo() with RowNo(TOTAL).

I hope it helps.