Forum Discussion

SamuraiEd's avatar
SamuraiEd
Icon for Helper I rankHelper 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?

     

SamuraiEd's avatar
SamuraiEd
Icon for Helper I rankHelper I
6 years ago

Im not sure if the translation worked correctly but it didnt seem to help.

I am thinking now that it would be easier to create a clicer and disabling the interactions with other tiles....

I do think there must be a solution to what im looking for though.!

amaleranda's avatar
amaleranda
Icon for Post Patron rankPost 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
    Icon for Helper I rankHelper 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
      Icon for Community Support rankCommunity 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?