Calculate percentage change

[harlickwin]

Hi Guys,

Slowly learning PowerBI, but this has me stumped..

I have a very simple table, from an internal report that is generated (roughly) every 30 days.

Name        |    Date1     |     Date2     |    Date3    |     Date4    
Person1          3532             5843           6739           7537 
Person2          1163             2481           3693           4348

I'd like to graph the percentage change (increase) between each time the report is run. I'm assuming I need to add a new column between each date, to calculate the percentage change between the previous 2 values... But that would mean there will be a manual process every time the report is updated, right?

Is there a way to dynamically calculate (and graph) this everytime the report is run, or will i need to add a new column to calculate the % change each time?

Cheers

Is there a way to have this calculation autom

[v-caliao-msft · Accepted solution]

@harlickwin,

You need to unpivot your data, and then cretea a measuer to calculate the percentage change.

Percentage change =
var previousdate = CALCULATE(MAX(MonthlyData[Attribute]),FILTER(ALLEXCEPT(MonthlyData,MonthlyData[Name]),MonthlyData[Attribute]<MAX(MonthlyData[Attribute]))
)
var previousvalue = LOOKUPVALUE(MonthlyData[Value],MonthlyData[Attribute],previousdate,MonthlyData[Name],MAX(MonthlyData[Name]))
return IF(ISBLANK(previousvalue),BLANK(),(SUM(MonthlyData[Value])-previousvalue)/previousvalue)

Regards,

Charlie Liao

[TheOckieMofo]

Your best bet is to actually manipulate the data via Power Query (Get & Transform in PBI). Your data is currently "pivoted." A database would not be designed this way. When you bring in the data form your source, you can edit your query. On the "Transform" tab, you will see an option called "unpivot Columns." What you need to do is select the column for [Name] in your data. Then click the down arrow next to "unpivot columns" and select "Unpivot Other Columns." This will "unpivot" your data to essentially give you a three column table with Name, attribute, and value. Attribute here would be your Date field and value would be the value (maybe that's sales?). Obviously, you can rename the columns to make more sense. With the query set up this way, as new columns are added to your source data, the new columns will still be unpivoted providing you with an ever expanding table.

Now that your data is shaped properly, the formulas become much easier. In fact, since you have time-based data, DAX (the formula language that drives the data modeling in Power BI) has numerous built in time calculations that you can use, depending on the frequency of your data. To learn more about those, I would highly recommend Rob Collie's book, "Power Pivot and Power BI" as well as his blog, powerpivotpro.com. Also, to learn more about the query engine, I would check out Ken Puls book "M is for Data Monkey" and his blog at excelguru.ca.

Hopefully that should point you in the right direction.