Forum Discussion

avinrosh's avatar
avinrosh
Frequent Visitor
8 years ago
Solved

Not getting Cumulative sum using measure

Hello;

 

I have wrote a measure to calculate the cumulative sum of Revenue, till the month before from the current month.

I am able to get the revenue for all the previous months but I am not getting it as a cumulative value.

 

I have used following measure

 

RevenueActual = CALCULATE(sum(Revenue[RevenueInGBP]),filter(Revenue, Revenue[Sale/Purchase]="Sales" && Revenue[Month]< EOMONTH( today(),-1))) 

 

Please help me how should I proceed to get the cumulative value.

Thanks

Tips and Tricks
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi avinrosh,

     

    Are you trying to achieve the accumulate total of each row which can meet your conditions? If so, modify you measure like this pattern and check if it can work:

     

    RevenueActual =
VAR last_month =
    EOMONTH ( MAX ( Revenue[Month] ), -1 )
RETURN
    CALCULATE (
        SUM ( Revenue[RevenueInGBP] ),
        FILTER (
            Revenue,
            Revenue[Sale/Purchase] = "Sales"
                && Revenue[Month] < last_month
        )
    )

    Hope it's helpful.

     

    Jimmy Tao

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi avinrosh,

     

    Are you trying to achieve the accumulate total of each row which can meet your conditions? If so, modify you measure like this pattern and check if it can work:

     

    RevenueActual =
VAR last_month =
    EOMONTH ( MAX ( Revenue[Month] ), -1 )
RETURN
    CALCULATE (
        SUM ( Revenue[RevenueInGBP] ),
        FILTER (
            Revenue,
            Revenue[Sale/Purchase] = "Sales"
                && Revenue[Month] < last_month
        )
    )

    Hope it's helpful.

     

    Jimmy Tao