Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative Sum is not working as expected

Hi All,

        1.  I have written a mesure like below to get the cumulative sum

 

2. Its working as expected when no field is added in the columns section like below

 

 

3. But my requirement is to get the cumulative sum for all the plants in the 'Plant' filed. When i added it its not working like below.

I have values for 1511 and 1512 also but its showing values only for 1526. Please help on this. Thanks in advance.

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    Add a Date table to your model and connect it to 'F/Unrealeased_PO' table on date columns. Then use Year and Month columns from Date table into the matrix as Rows fields. Then use below measure. I create a sample pbix for your reference.

    CumulativeTotal = 
VAR _date = MAX ( DimDate[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Total Purchase] ),
        ALL ( DimDate[Date] ),
        DimDate[Date] <= _date
    ) + 0

     

    Best regards,

    Jing

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous 

     

    Try this measure.

    CumulativeTotal =
VAR _date = MAX ( DimDate[Date] )
RETURN
    CALCULATE (
        SUM ( 'F/Unrealeased_PO'[OpenPODelValue_USD] ),
        ALL ( 'F/Unrealeased_PO'[PO Due Date] ),
        'F/Unrealeased_PO'[PO Due Date] <= _date
    ) + 0

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-jingzhang thanks for your solution but still its not working for me. Can you please give any other solution.

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous 

         

        Add a Date table to your model and connect it to 'F/Unrealeased_PO' table on date columns. Then use Year and Month columns from Date table into the matrix as Rows fields. Then use below measure. I create a sample pbix for your reference.

        CumulativeTotal = 
VAR _date = MAX ( DimDate[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Total Purchase] ),
        ALL ( DimDate[Date] ),
        DimDate[Date] <= _date
    ) + 0

         

        Best regards,

        Jing