MOM with condition (filter)

[bideveloper555]

hi,

@amitchandak has answered MOM but i need to implement condition .

https://community.powerbi.com/t5/Desktop/Comparing-Last-month-last-3-months-and-last-12-month-to-las...

if am doing 3 mom in mar 2021.

jan to mar 2020(3 months)

jan to mar 2021(3 months)

but in any of total 6 months has value of Zero or blank, than 3 mom will be blank, no measure required.

Condition is sum of total per month should be greather than 0.

Thanks

Vinay

[bideveloper555]

hi amit,

what i did was added extra column, aggregating over to month.

mom =

var vl = CALCULATE(SUM('Table (2)'[value]),FILTER('Table (2)','Table (2)'[Dateid].[MonthNo]= EARLIER('Table (2)'[Dateid].[MonthNo]) && 'Table (2)'[Dateid].[Year] = EARLIER('Table (2)'[Dateid].[Year])))

var v2 = IF(vl = 0,BLANK(),vl)

return v2

Than using your measure with IF statement as per condition i needed.

TurnoverAmount MoM% =

var _pcnt = CALCULATE(DISTINCTCOUNTNOBLANK('Table (2)'[mom]),DATESINPERIOD('Date'[Dateid],ENDOFMONTH(dateadd('Date'[Dateid],-12,MONTH)),-3,MONTH))

    VAR __PREV_MONTH = IF(_pcnt = 3,

        CALCULATE(

            SUM('Table (2)'[value]),DATESINPERIOD('Date'[Dateid],ENDOFMONTH(dateadd('Date'[Dateid],-12,MONTH)),-3,MONTH)

        ))

var _ccnt = CALCULATE(DISTINCTCOUNTNOBLANK('Table (2)'[mom]),DATESINPERIOD('Date'[Dateid],ENDOFMONTH('Date'[Dateid]),-3,MONTH) )

var __CURRENTMONTH = IF(_ccnt = 3, CALCULATE(SUM('Table (2)'[value]),DATESINPERIOD('Date'[Dateid],ENDOFMONTH('Date'[Dateid]),-3,MONTH)))

Var _SalesYOY = IF (

NOT ISBLANK ( __CURRENTMONTH )

&& NOT ISBLANK ( __PREV_MONTH ),

__CURRENTMONTH - __PREV_MONTH

)

    RETURN

    DIVIDE(

            _SalesYOY,

            __PREV_MONTH

        )

My naming are bad, mom = sum of value over month.

example: when i selected may 2021 3 MOM, i dont expect to see result as i dont have sales in April 2021.

Thanks, your resources helps me always.

[amitchandak · Accepted solution]

@bideveloper555 , If you have date selected from date table

Rolling 3= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

Rolling 3 last year = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-12) ,-3,MONTH))

But remember this will rolling 3 one value for 3 months, not a trend as one value is suggested.

Also, refer

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI