Help on cumulative calculation

[Anonymous]

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

[v-zhenbw-msft]

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.

[Anonymous]

hi

unable to access the PBIX.

[v-zhenbw-msft]

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.

[Anonymous]

hi.. i saw the pbix. My requiremnt is 

If I select all the months( ACT dataset),  the value in the bar should come 0 for Mar and April. Though the value for Jan and Feb is correct ( as per cummulative)  .

[v-zhenbw-msft · Accepted solution]

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.

[Anonymous]

perfect

[amitchandak]

@v-zhenbw-msft , is it for me ?

[v-zhenbw-msft]

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.

[amitchandak]

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]

Not working. This is not giving the desired result.