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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper V

## Cumulative Sum of 2 Rows

Hi guys,

Above is the formula I created in Excel. Row 4 is what I'm trying to create in Power BI, but I'm not sure how do I achieve that.

Here is the Dax function I wrote but I'm not getting the correct value in November and December

`Measure = CALCULATE([Total]),FILTER(ALLSELECTED(Table1),Table1[Month Num] <= Max(Table1[Month Num])))`

Can someone please help me?

Thank you

1 ACCEPTED SOLUTION
MVP

Hi @Stuznet

```Row3 =
VAR Row1Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +1 >= MONTH(TODAY())))
VAR Row2Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +2 <= MONTH(TODAY()) && Data[ROW] <> BLANK()))
RETURN
Row1Sum + Row2Sum```
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast

9 REPLIES 9
Super User

Hi,

What is actually there in the first column.  Are they years by any chance?  Can you share your PBI file with dummy figures?

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper V

@Ashish_Mathur

Here is the dummy report Cumulative Sum of 2 Rows, it is exactly what I'm trying to achieve on my active report.

Row 3 accumulating from Row 2. IF November and December <> BLANK(), I want it to accumulate from Row1 starting from October to December.

Note: Row 3 November to December number is the wrong result.

Thank you so you so much for looking into this.

MVP

Hi @Stuznet

```Row3 =
VAR Row1Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +1 >= MONTH(TODAY())))
VAR Row2Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +2 <= MONTH(TODAY()) && Data[ROW] <> BLANK()))
RETURN
Row1Sum + Row2Sum```
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast

Helper V

@mwegener

I could not thank you-you enough, I could have never thought of that approach.

Thank you again 🙂

Super User

Hi,

Sorry i cannot help with this one.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MVP

Hi @Stuznet,

maybe this helps.

`Measure = IF(HASONEFILTER(Table1[Row]);SUM(Table1[Value]);CALCULATE(SUM(Table1[Value]);FILTER(ALLSELECTED(Table1);Table1[Month Num] <= Max(Table1[Month Num]))))`

Best regards,

Marcus

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast

Helper V

@mwegeber,
Thank you for providing your solution, that’s the correct value I’m looking for but I forgot to mention that in power bi, I created row 2 and 3 with a separate measures

```Row 2 =
VAR abc = CALCULATE( [total] / 1000000
RETURN
IF(MAX( Table [Month Num] + 1 >= MONTH(TODAY()), abc , BLANK())```

```Row3 =
VAR def = CALCULATE( [total], Filter( Table, Table[ Date] <> BLANK())) / (1000000)
RETURN
IF(MAX( Table [Month Num] + 2 >= MONTH(TODAY()), def , BLANK()```

When I plugged in the measure in your solution I’m not getting the result

```Measure = IF(HASONEFILTER( Table[Row]), CALCULATE(
[Row2] + [Row3] , FILTER(
ALLSELECTED( Table) , Table[Month Num] <= MAX(Table[Month Num]))))```

Could you please advise? Thank you

MVP

Hi @Stuznet

`Measure = CALCULATE([Row2]+[Row3],FILTER(ALLSELECTED(Table1),Table1[Month Num] <= Max(Table1[Month Num])))`

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast

Helper V

I wish I can share the data, but my work computer is pretty locked down. I did try the 2nd functions you provided, I'm not getting the correct results.

Is there a way I can write a Cumulative with Conditional?

For Example,

`Measure = If [Row 3]  <> BLANK(), Sum [Row 2] that is not BLANK`

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors