Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

PowerBI Compare Data and show % difference

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
goncalogeraldes
Icon for Super User rankSuper User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    goncalogeraldes
    Icon for Super User rankSuper User
    4 years ago
    % 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's avatar
      Anonymous
      Not applicable
      4 years ago

      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?