cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

1 ACCEPTED SOLUTION
Resident Rockstar

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

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

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

 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

Resident Rockstar

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

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors