CUMULATIVE PER QUARTER

[Anonymous]

Hi all,

I need your help, I have a table similar to this style but I need to do the cumulative revenue.

TOTAL INVESMENT	QUARTER	FY
$ 288,521	Q4	FY17
$ 151,918	Q1	FY18
$ 35,850	Q2	FY18
$ 61,265	Q3	FY18
$ 202,410	Q4	FY18

my final table should be left with a column like this

TOTAL INVESMENT	QUARTER	FY	Cumulative
$ 288,521	Q4	FY17
$ 151,918	Q1	FY18	$ 440,439
$ 35,850	Q2	FY18	$ 476,289
$ 61,265	Q3	FY18	$ 537,554
$ 202,410	Q4	FY18	$ 739,964

I used this formula but it does not work:

CUMULATIVE INVESTMENT = CALCULATE(SUM('POS EXECUTED'[INVESTMENT]);FILTER(ALL('POS EXECUTED'[QUARTER]);'POS EXECUTED'[QUARTER] <= MAX('POS EXECUTED'[QUARTER])))

Help Me, Please

[Anonymous]

Hi,

I'm assuming in your data you have actual date values somewhere in addition to just the Quarter and FY text values. I think something along the lines of this calc will work for you. I took your dataset and plugged it into excel, and added a random date column that corresponded with the Q and FY (just made an assumption that the fiscal year was a traditional calendar year):

I then used the following calc:

Running Total =

CALCULATE (

SUM ( Sheet1[Total Investment] ),

FILTER (

ALL ( Sheet1 ),

Sheet1[Date] <= MAX ( Sheet1[Date] )

)

)

And it produced the results you provided above that you're expecting:

Let me know if this helps.

Thanks,

Ben

[Anonymous]

Thanks for the help. apply this same method that you indicate me but when I take the info to only see by Q and FY it stops showing the accumulated

[Anonymous]

I think you'll need to leverage a date column here, rather than just those two other columns.

[Anonymous]

Hello ,

I continue with my problem, because the idea is to see the data summarized by Quarter and fiscal year, but by placing between my elements the Q this fails and if I set the FY values ​​separates me, I only summarize each Q according to the year.

I need to be able to have something like this:

YEAR        QUARTER  KEY          REVENUE            ACUMM

FY17	Q1	FY17Q1	$       5,989	$    5,989
FY17	Q2	FY17Q2	$       5,689	$  11,678
FY17	Q3	FY17Q3	$          487	$  12,165
FY17	Q4	FY17Q4	$       5,589	$  17,754
FY18	Q1	FY18Q1	$       3,645	$  21,399
FY18	Q2	FY18Q2	$     11,223	$  32,622
FY18	Q3	FY18Q3	$       5,647	$  38,269
FY18	Q4	FY18Q4	$       3,365	$  41,634
FY19	Q1	FY19Q1	$       1,200	$  42,834

also if I take these values ​​to a dynamic table, have the option to see them accumulated independent of the filter that is:

	FY18Q1	FY18Q2	FY18Q3	FY18Q4	FY19Q1
Reven Acumm	$ 21,399	$ 32,622	$ 38,269	$ 41,634	$ 42,834

I hope to make myself understood. Thank you so much for your help

[v-piga-msft · Accepted solution]

Hi @Anonymous,

It seems that you want to calcualted the cumulative per quarter, you could try the steps below.

1. Create an index column in query editor;

2. Create the measure with the formula below.

Measure =
CALCULATE (
    SUM ( 'Table1'[ACUMM] ),
    FILTER ( ALLSELECTED ( 'Table1' ), 'Table1'[Index] <= MAX ( 'Table1'[Index] ) )
)

Here is you desired output.

Best Regards,

Cherry