Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Comparing variations between actual month and last month

Hello everybody, 

 

I am trying to get the variations between the revenue of the current month and the last one, but only these two without including the rest of the date in the data.

 

So I tried 

currentmonthsum  = TOTALMTD([Original Value SUM];Revenue[Date];DATESBETWEEN(Revenue[Date];01;31)) 
 
It's doenst work apparentely, could someone guide me?
Need Help
Show and Tell
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    You need a date dimention table first. Also, add a calculated column [index] in it.

    Date = CALENDAR(MIN(Revenue[Date]),MAX(Revenue[Date]) )

    index = RANKX('Date',YEAR([Date])*100+MONTH([Date]),,ASC,Dense)
     
    Establish a one to many relationship from date table to Revenue table based on [date] field.
     
    Then, add 'Date'[Date] and below two measures into table visual.
    TM revenue = SUM(Revenue[Amount])

LM revenue = CALCULATE([TM revenue],FILTER(ALLSELECTED('Date'),'Date'[index]=MAX('Date'[index])-1))
     
    Best regards,
    Yuliana Gu

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Good information, but still didnt resolve my problem. I need to sum the revenue of this month, and compare to the last month revenue sum, and then see the variaton. Thanks

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        7 years ago
        That's a very minor variation on those formulas. I was trying to just point you to how to do it versus doing it for you. TITHW_TotalMonth = VAR __MaxYear = MAX('Years'[Year]) VAR __MaxMonth = MAX('Months'[MonthSort]) VAR __TmpTable = CALCULATETABLE('TheHardWay',ALL('Years'[Year]),All('Months'[Month])) RETURN SUMX(FILTER(__TmpTable,[Year]=__MaxYear && [MonthSort] = __MaxMonth),[Value]) TITHW_TotalLastMonth = VAR __MaxYear = MAX('Years'[Year]) VAR __MaxMonth = MAX('Months'[MonthSort]) VAR __TmpTable = CALCULATETABLE('TheHardWay',ALL('Years'[Year]),All('Months'[Month])) RETURN SUMX(FILTER(__TmpTable,[Year]=__MaxYear-1 && [MonthSort] = __MaxMonth-1),[Value]) TITHW_%ChangeMoM = VAR __MaxYear = MAX('Years'[Year]) VAR __MaxMonth = MAX('Months'[MonthSort]) VAR __TmpTable = CALCULATETABLE('TheHardWay',ALL('Years'[Year]),All('Months'[Month])) VAR __currentMonth = SUMX(FILTER(__TmpTable,[Year]=__MaxYear && [MonthSort] = __MaxMonth),[Value]) VAR __previousMonth = SUMX(FILTER(__TmpTable,[Year]=__MaxYear - 1 && [MonthSort] = __MaxMonth-1),[Value]) RETURN DIVIDE(__currentMonth - __previousMonth,__previousMonth,0) Something like that, might have to take into account January and December boundary case.
  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

    You need a date dimention table first. Also, add a calculated column [index] in it.

    Date = CALENDAR(MIN(Revenue[Date]),MAX(Revenue[Date]) )

    index = RANKX('Date',YEAR([Date])*100+MONTH([Date]),,ASC,Dense)
     
    Establish a one to many relationship from date table to Revenue table based on [date] field.
     
    Then, add 'Date'[Date] and below two measures into table visual.
    TM revenue = SUM(Revenue[Amount])

LM revenue = CALCULATE([TM revenue],FILTER(ALLSELECTED('Date'),'Date'[index]=MAX('Date'[index])-1))
     
    Best regards,
    Yuliana Gu