PowerBI Compare Data and show % difference

[Anonymous]

Hi I have an excel document which gets updated monthly with the stats from that month. I want to be able to compare the data to show the increase in the numbers. The numbers will either stay the same or go up. I want to compare it off the first month in the data set. 

Date	Total Completed Tasks	Total In Progress Tasks
01/08/2021	40	2
01/09/2021	50	6
01/10/2021	64	10

So using August as the starting month i want to be able to show the % incrsase in completed tasks over the months. How is this possible to report on?

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

You can modify the function to the following form:

completed tasks =
var _taskfirstmonth = CALCULATE( [TotalReq], PREVIOUSMONTH(Sheet1[Date].[Date]))
var _increase = [TotalReq] - _taskfirstmonth
return
DIVIDE(_increase, _taskfirstmonth, 0)

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.

[goncalogeraldes]

Hello there @Anonymous ! Try this:

% increase in completed tasks =
var _taskfirstmonth = MIN(Table[Total In Progress Tasks])
var _tasks = MAX(Table[Total In Progress Tasks])
var _increase = _tasks - _taskfirstmonth

return
DIVIDE( _increase, _tasks, 0)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[Anonymous]

Hi @goncalogeraldes, 

Thanks for that it appears to be what i am looking for. 1 question i do have though is as i am currently running this near the end of the year when i look at the data in a table i shows be all the months (January etc) with a -100% change. is there a way to exclude these so it only looks at the data that is avilable? same with its showing me November/December when that data is not yet present. 

[goncalogeraldes]

% increase in completed tasks =
var _taskfirstmonth = MIN(Table[Total In Progress Tasks])
var _tasks = MAX(Table[Total In Progress Tasks])
var _increase = _tasks - _taskfirstmonth

return
CALCULATE(
DIVIDE( _increase, _tasks, 0),
FILTER(Table, Table[Date] <= TODAY())
)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[Anonymous]

Hi @goncalogeraldes,

Apologies, i have changed it a little to show the percentage increase month on month comparing it to the previous month value instead as i relised i would end up with crazy numbers. So I have a measure which will give me the totalCompleted which is: 

TotalReq = SUM(Table[Total In Progress Tasks])

So i have ammended your code to the following:

completed tasks = 

var _taskfirstmonth = CALCULATE( [TotalReq], PREVIOUSMONTH(Sheet1[Date].[Date]))
var _increase = [TotalReq] - _taskfirstmonth
var _totalreq = _taskfirstmonth + _increase
return
DIVIDE(_taskfirstmonth,_totalreq, 0)

However the result is nearly there but for instance for the first month its telling me the percentage increase is 75% instead of the 25% it is suposed to be. Is there a way to get it to show the correct value?