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

Hi all,,

sorry for the delay. amaleranda  I put in your formula but its the same result as my previous others. See below. The boxes with filter for this month year is highlighted in blue but im looking to achieve that without filters (white box).

Measure 1

CurrentMonthTotal =
Var CurrentTotal = [Total water consumption]
Var YearMonth = FORMAT(TODAY(),"YYYY-MM")
Var Result = CALCULATE(CurrentTotal,'Calendar'[YYYY-MM]=YearMonth)
Return
Result
Measure 2
TotalWaterConsumptionMOM Test 1 =
Var CM = [CurrentMonthTotal] ((note: I dont think it makes a difference having the currentmonthtotal. Total water consumption looks ok))
Var LM = CALCULATE('Data Water'[Total water consumption], PREVIOUSMONTH('Calendar'[Date]))
Var DeltaM = CM - LM
Return
IF(NOT ISBLANK(CM), DIVIDE(DeltaM,LM))
 
FYI My calendar table ranges from 1/1/2017 to 31/12/2020
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.