How to calculate cumulative of sales for products by months which are consecutive positives

[Likhitha]

Hello All,

I'm having sales data for months by products.I want to calclulate cumulative of sales for N months ,if the months have positive sales.Month Selection is dynamic.In my case i'm considering for 3 months.

[Likhitha]

Hello All,

Thank you so much for your solutions.

I created some DAX and got the solution.

SalesNew= if(calculate(sum('NewData'[Sales]),DATEADD(DateTable[Date],0,MONTH))>0 &&

calculate(sum('NewData'[Sales]),DATEADD(DateT[Date],-1,MONTH))>0 &&

calculate(sum('NewData'[Sales]),DATEADD(DateTable[Date],-2,MONTH))>0 ,

CALCULATE(sum('NewData'[Sales]),FILTER(ALL(DateTable[Date]),MONTH(DateTable[Date])>MONTH(MAX(DateTable[Date]))-3 && MONTH(DateTable[Date])<=MONTH(MAX(DateTable[Date])))))

[v-frfei-msft · Accepted solution]

Hi @Likhitha ,

We can create a meausre as below.

Measure = 
VAR ed =
    EDATE ( MAX ( 'Current Year'[Date] ), -3 )
VAR d =
    MAX ( 'Current Year'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Current Year'[Total Customer] ),
        FILTER (
            ALLSELECTED ( 'Current Year' ),
            'Current Year'[Date] > ed
                && 'Current Year'[Date] <= d
                && 'Current Year'[Total Customer] >= 0
        )
    )

[Tahreem24]

Hi @Likhitha ,

Use the below measure to your calculation:

Cumulative = CALCULATE([Total],FILTER(ALLSELECTED(Table1),Table1[Month Num] <= MAX(Table1[Month Num])))

Don't forget to hit Thumbs up and accept this as a solution if you find it helpful! 

[Likhitha]

@Tahreem24  In my case , if i select december month the cumulative sum should happen only if my november and october months have positive values,otherwise the product should be ignored.(I'm doing cumulative for 3 months).

[Tahreem24]

@Likhitha ,

Attached screen shot for your reference:

Please give KUDOS for support! 😊

[Tahreem24]

@Likhitha ,

Please try below measure:

Last 3 month Cumulative = CALCULATE(SUM('Current Year'[Total Customer]),DATESINPERIOD('Current Year'[Month],LASTDATE('Current Year'[Month]),-3,MONTH))

Please hit Thumbs up for support and accept this as a solution if it helps you!

[Likhitha]

@Tahreem24  In that DAX,how can i check that each month sales value is positive only??