Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help on cumulative calculation

My data set is  datset Month value ACT Feb 2 LMN feb 3 UFG Jan 2 ACT Jan 3 ACT Mar 0 LMN Mar 1 ACT April 0   I need column chart with  X axis Month Y a...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Anonymous ,

     

    We can nest an IF function outside the original formula to meet your requirement.

     

    Measure = 
IF (
    MAX ( 'Table'[value] ) = 0,
    0,
    VAR x =
        MIN ( 'Table'[month number] )
    RETURN
        CALCULATE (
            SUM ( 'Table'[value] ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[month number] <= x )
        )
)

     

    The result like this,

     

     

    For the sample, we add a new row, what is your expected result for May, 6 or 1?

     

     

     

    BTW, pbix as attached.

     

    Best regards,


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

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

Hi amitchandak ,

 

We can use the following steps to meet your requirement.

1. Create a month number column, add the corresponding number to the month,

 

 

month number = MONTH('Table'[date])

 

 

 

 

 

 

 

 

 

2. Then create a measure,

 

 

Measure =
var x = MIN('Table'[month number])
return
CALCULATE(SUM('Table'[value]),FILTER(ALLSELECTED('Table'),'Table'[month number]<=x))

 

 

And add two slicer to control the measure, we can get the result like this,

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

BTW, pbix as attached. 

 

Best regards,


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

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    Hi amitchandak ，

     

    Sorry @wrong people.

     

    Best Regards,


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