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?

     

SamuraiEd's avatar
SamuraiEd
Helper I
6 years ago

Im sorry I am not sure if this is the way to attach source data.

MonthDomesticConsumptionNeWaterConsumption

1-Jan-19 1,305.60 2,538.20
1-Feb-19 1,963.90 2,567.30
1-Mar-19 2,220.20 2,788.40
1-Apr-19 1,989.40 2,546.20
1-May-19 2,203.60 2,514.30
1-Jun-19 2,248.20 2,936.70
1-Jul-19 2,546.10 2,132.80
1-Aug-19 2,630.80 2,592.90
1-Sep-19 2,717.30 2,922.30
1-Oct-19 2,782.00 2,757.60
1-Nov-19 865.30 2,234.50
1-Dec-19 1,912.90 2,662.40
1-Jan-20 2,863.40 2,035.00
1-Feb-20 2,867.50 2,424.40
1-Mar-20 2,107.40 1,308.10
1-Apr-20 1,200.80 1,896.80
1-May-20 529.00 1,958.20
1-Jun-20 600.00 1,774.60
1-Jul-20 710.00 1,800.30
1-Aug-20 1,231.90 2,043.20

I also think the screenshot might be blurred so here it is again.

Annotation 2020-08-25 083439.png

amaleranda's avatar
amaleranda
Post Patron
6 years ago

SamuraiEd ,

 

Try using below measure for the Previous month. for this measure to work you also need [Year Month] column calculated in your calander tabel. from this measure you can workout % change over the last month. Not sure what are your slicer requirments. it turns blank since other filers on the page has an effect on the card. try turn off visual intractions with the card to the rest of the visuals on the page.

 

=======year month Column==========

"Year Month",FORMAT([Date],"YYYYM")

 

=======Previous Month Total measure==========

PreviousMonthTotal =
VAR CurrentTotal = SUM ( Sheet1[Total] )
VAR CurrentYear = YEAR(TODAY())
VAR PreviousMnth = MONTH(TODAY()) - 1
VAR YearMnth = CONCATENATE(CurrentYear,PreviousMnth)

VAR Result =
CALCULATE( [Current Total], 'Date'[Year Month] = YearMnth)
RETURN
Result
  • SamuraiEd's avatar
    SamuraiEd
    Helper 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
      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.