cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
judato88 Frequent Visitor
Frequent Visitor

CUMULATIVE PER QUARTER

Hi all,

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

 

 

TOTAL INVESMENTQUARTERFY
$ 288,521Q4FY17
$ 151,918Q1FY18
$ 35,850Q2FY18
$ 61,265Q3FY18
$ 202,410Q4FY18

 

my final table should be left with a column like this

 

TOTAL INVESMENTQUARTERFYCumulative
$ 288,521Q4FY17 
$ 151,918Q1FY18$ 440,439
$ 35,850Q2FY18$ 476,289
$ 61,265Q3FY18$ 537,554
$ 202,410Q4FY18$ 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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: CUMULATIVE PER QUARTER

Hi @judato88,

 

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;

 

add index.PNG

 

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.

 

Capture.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
bhpage Regular Visitor
Regular Visitor

Re: CUMULATIVE PER QUARTER

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):

 

Cumulative per Quarter.PNG

 

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:
 
Cumulative per Quarter 2.PNG
 
Let me know if this helps.
 
Thanks,
Ben
judato88 Frequent Visitor
Frequent Visitor

Re: CUMULATIVE PER QUARTER

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

bhpage Regular Visitor
Regular Visitor

Re: CUMULATIVE PER QUARTER

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

judato88 Frequent Visitor
Frequent Visitor

Re: CUMULATIVE PER QUARTER

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

FY17Q1FY17Q1 $       5,989 $    5,989
FY17Q2FY17Q2 $       5,689 $  11,678
FY17Q3FY17Q3 $          487 $  12,165
FY17Q4FY17Q4 $       5,589 $  17,754
FY18Q1FY18Q1 $       3,645 $  21,399
FY18Q2FY18Q2 $     11,223 $  32,622
FY18Q3FY18Q3 $       5,647 $  38,269
FY18Q4FY18Q4 $       3,365 $  41,634
FY19Q1FY19Q1 $       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:

 

 FY18Q1FY18Q2FY18Q3FY18Q4FY19Q1
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

Community Support Team
Community Support Team

Re: CUMULATIVE PER QUARTER

Hi @judato88,

 

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;

 

add index.PNG

 

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.

 

Capture.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.