Comparing variations between actual month and last month

[Anonymous]

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?

[v-yulgu-msft · Accepted solution]

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

[Greg_Deckler]

See if my Time Intelligence The Hard Way gets you what you need: https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

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]

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.