Forum Discussion

SamuraiEd's avatar
SamuraiEd
Helper I
6 years ago
Solved

Month on Month - error

Hi, I am sure this is an easy one for the pros. I am struggling though.   I am trying to do what should be a simple MoM % but it keeps showing the sum of the MoM% as the final figure when I put the...
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi SamuraiEd ,

     

    I find that you have tried the formula below:

    TotalWaterConsumptionMOM Test 1 = 
Var CM = [CurrentMonthTotal] 
Var LM = CALCULATE('Data Water'[Total water consumption], PREVIOUSMONTH('Calendar'[Date]))
Var DeltaM = CM - LM
Return
IF(NOT ISBLANK(CM), DIVIDE(DeltaM,LM))

     

    You could create a new measure to summarize it to get the total value.

    Measure = SUMX('Data Water',[TotalWaterConsumptionMOM Test 1])

     

    Is the result what you want?

     

amaleranda's avatar
amaleranda
Post Patron
6 years ago

SamuraiEd ,

 

challange here is to work out what did you want to do when user make a selction, when a user select a different month from a then card visual returns blank as there would either no previous month total or current month total. My understanding was you need to see the % comparing previous month to current month. There's no visual level filter on the card.  

 

 

SamuraiEd's avatar
SamuraiEd
Helper I
6 years ago

Thank you. Yes I am looking at a one card like you have showing MOM%.

 

Previous Month

PreviousMonthTotal =
Var CurrentTotal = 'Data Water'[Total water consumption]
Var CurrentYear = YEAR(TODAY())
Var PMonth = MONTH(TODAY())-1
VAR Yearmonth = CONCATENATE(CurrentYear,PMonth)
Var Result = CALCULATE(CurrentTotal,'Calendar'[Year Month]=Yearmonth)
Return
Result
 
Current Month
CurrentMonthTotal =
Var CurrentTotal = [Total water consumption]
Var YearMonth = FORMAT(TODAY(),"YYYY-MM")
Var Result = CALCULATE(CurrentTotal,'Calendar'[YYYY-MM]=YearMonth)
Return
Result
 
My Calendar looks like this:
 
 
My Current Month and Previous Month results appear to be the same too.
Really not sure if i have missed out one of your instructions.
 
Ill give this one more go, then probaby have to resort to a slicer. 
  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi SamuraiEd ,

     

    I find that you have tried the formula below:

    TotalWaterConsumptionMOM Test 1 = 
Var CM = [CurrentMonthTotal] 
Var LM = CALCULATE('Data Water'[Total water consumption], PREVIOUSMONTH('Calendar'[Date]))
Var DeltaM = CM - LM
Return
IF(NOT ISBLANK(CM), DIVIDE(DeltaM,LM))

     

    You could create a new measure to summarize it to get the total value.

    Measure = SUMX('Data Water',[TotalWaterConsumptionMOM Test 1])

     

    Is the result what you want?