Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help on cumulative calculation

My data set is 

datsetMonthvalue
ACTFeb2
LMNfeb3
UFGJan2
ACTJan3
ACTMar0
LMNMar1
ACTApril0

 

I need column chart with 

X axis Month

Y axis value

and chart to be shown cummulative value for ACT datasets only. The value( bar) to be shown for month Jan, Feb only but no value should be shown for Mar and April

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.

10 Replies

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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hi

       

      unable to access the PBIX.

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

        Hi Anonymous ,

         

        Sorry for that, here is another link, you can try again.

         

        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.