Forum Discussion
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...
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
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.