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 ,

 

Create a Variabel to get the current month as below and then put that in the CM measure to filter out only the current year month. make sure you have a column in you calader tabel called year month to the format of "YYYY-MM". Then use below variable in the CM measure to only filter out dates from the calendar tabel. it should give you only the current month value. 

Your current CM measuer does not filter out any dates hence it returns sum of charges to the card. if you use a table of matrix with month on row it should work, but in a card visual theres not filter context coming to the aggragrates unless you use visual level filters.

 

Current Total =
VAR Result =
SUM ( Sheet1[Total] )
RETURN
Result

 

CurrentMonthTotal =
VAR CurrentTotal = [Current Total]
VAR YearMonth = FORMAT(TODAY(),"YYYY-MM")
VAR Result = CALCULATE([Current Total], 'Date'[Year Month] = YearMonth)
RETURN
Result
 
hope this helps, if it works for you please acccpet this as a solution.