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.

amitchandak's avatar
amitchandak
Super User
6 years ago

In case you have date, You can create cumulative like this

Cumm Sales = CALCULATE(SUM(Table[Value]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Table[Value]),filter(date,date[date] <=max(Table[Date])))

 

What is format you have month in you raw data

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Not working. This is not giving the desired result.