Make a percentage change formula

[DouglasWatkins]

I have a long list with multiple entries per month.  I want to know how to write a formula that would show the percentage change per month of the Impressions column.

Thank you in advance.

[v-yangliu-msft · Accepted solution]

Hi  @DouglasWatkins ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _startdate=
DATE(YEAR(MAX('Table'[Date])),MONTH(MAX('Table'[Date])),1)
var _enddate=
EOMONTH(_startdate,0)
var _laststartdate=
DATE(YEAR(MAX('Table'[Date])),MONTH(MAX('Table'[Date]))-1,1)
var _lastenddate=
EOMONTH(_laststartdate,0)
var _thissum=
SUMX(FILTER(ALL('Table'),'Table'[Date]>=_startdate&&'Table'[Date]<=_enddate),[Impressions])
var _lastsum=
SUMX(FILTER(ALL('Table'),'Table'[Date]>=_laststartdate&&'Table'[Date]<=_lastenddate),[Impressions])
return
DIVIDE( _thissum - _lastsum,_thissum)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[FreemanZ]

seems you are expecting a measure. But measures depend on context, how would you like to present your data? A table visual? What field will you pull to the axis? you would depict it with excel table. 

[DouglasWatkins]

I will show it in a bar or line chart.  I probably need to create a column that sums the Impressions by month.  Then I would like to use that column to create a column or measure which I can use with a connected date table to create a bar/line chart.

[FreemanZ]

indeed there are multiple ways, it would be super easier if you have a Date table. 

Create three measures:

ImpressionSum = SUM(TableName[Impressions])

ImpressionSumPM =

CALCULATE([ImpressionSum], DATEADD('Date'[Date], -1, MONTH))

ImpressionVariance= FORMAT( DIVIDE(ImpressionSum-ImpressionSumPM, ImpressionSumPM), "00.0%")

 

Plot the chart visual with measure [ImpressionVariace] and use Date with monthly granularity on the axis.