Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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;

 

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.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Not applicable

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

Anonymous
Not applicable

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

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;

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.