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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hey v-jingzhang thank you very much for your solution. It's working as per my requirement. Thanks alot..