Forum Discussion

bideveloper555's avatar
bideveloper555
Helper IV
4 years ago
Solved

MOM with condition (filter)

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-last/m-p/893894

 

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

Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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

  • bideveloper555's avatar
    bideveloper555
    Helper IV
    4 years ago

    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.