Forum Discussion

Stuznet's avatar
Stuznet
Helper V
7 years ago
Solved

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 ...
Need Help
Show and Tell
Tutorial Requests
  • mwegener's avatar
    mwegener
    7 years ago

    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
Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

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

Stuznet's avatar
Stuznet
Helper V
7 years ago

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.

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    7 years ago

    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
    • Stuznet's avatar
      Stuznet
      Helper V
      7 years ago

      mwegener

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

       

      Thank you again :) 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Sorry i cannot help with this one.