cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RodrigoTXRA
Helper I
Helper I

YTD performance

Hi All


I'm trying to replicate a calculation I have in excel into Power BI.

Long story short, I have a table which display what the performance is for each month (see screen shots) and a second column YTD where it displays the performance depending on the month.

 

i.e. If we had 100% in July and 120 % in August, then the YTD column would display 100% for July and 110% for August as August is the sum of July and August percentages divided by 2.

 

Then if it was september, it would be divided by 3 and so on.

 

I know how to create this calculation in Excel, but would any of you know how to do in Power BI?


See Power BI Result.PNGPower BI.PNGscreen shots

1 ACCEPTED SOLUTION

@RodrigoTXRAHere is something I put together to give you an idea, logically you should have a FIscal Date DImension in model for time intelligence and this kind of calculation, but I guess this will get you started.

 

Cheers!!!

P






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

11 REPLIES 11
parry2k
Super User
Super User

@RodrigoTXRACan you share the sample data set and will get back to you with the solution. Thanks!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi Parry

Thanks for the quick reply, please find below the dataset.

When i paste, the headers are deleted but it is as follows:

Sales Rep | User ID | FY Appraisal Month | Appraisal Month % | YTD | Sort | Quarter

Full NamexxxxxxxxxxxxxJul-18100.00%100.00%1Q1
Full NamexxxxxxxxxxxxxAug-18120.00%110.00%2Q1
Full NamexxxxxxxxxxxxxSep-18126.21%115.40%3Q1
Full NamexxxxxxxxxxxxxOct-18120.00%116.55%4Q2
Full NamexxxxxxxxxxxxxNov-18110.00%115.24%5Q2
Full NamexxxxxxxxxxxxxDec-18120.00%116.04%6Q2
Full NamexxxxxxxxxxxxxJan-19110.00%115.17%7Q3
Full NamexxxxxxxxxxxxxFeb-19130.00%117.03%8Q3
Full NamexxxxxxxxxxxxxMar-19110.00%116.25%9Q3
Full NamexxxxxxxxxxxxxApr-19140.00%118.62%10Q4
Full NamexxxxxxxxxxxxxMay-19137.00%120.29%11Q4
Full NamexxxxxxxxxxxxxJun-19130.00%121.10%12Q4

@RodrigoTXRAwill look at this later today and get back to you.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Is your YTD definition is Jan - Dec or different?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k, our Financial Year goes from July to June the following year.

i.e. From 01st July 2017 to 30th June 2018

@RodrigoTXRAHere is something I put together to give you an idea, logically you should have a FIscal Date DImension in model for time intelligence and this kind of calculation, but I guess this will get you started.

 

Cheers!!!

P






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Apparently I cannot send too many private messages, please see below

@parry2k - "Great, thanks Parry - I don't think there is an area where I can upload a file so I pasted it below.

I have inserted three users in this list below but I will have lists with 10 users at time.

In regards to the learning curve, I hope one day to be able to also give it back to the community, I'm building my skill sets at the moment and attending many meet ups with Power Bi users.

 

Sales RepUser IDFY Appraisal MonthAppraisal Month %SortQuarter
Mary Janeijkl123Jul-18107.40%1Q1
Mary Janeijkl123Aug-18120.00%2Q1
Mary Janeijkl123Sep-18126.21%3Q1
Mary Janeijkl123Oct-18120.00%4Q2
Mary Janeijkl123Nov-18110.00%5Q2
Mary Janeijkl123Dec-18120.00%6Q2
Mary Janeijkl123Jan-19110.00%7Q3
Mary Janeijkl123Feb-19130.00%8Q3
Mary Janeijkl123Mar-19110.00%9Q3
Mary Janeijkl123Apr-19140.00%10Q4
Mary Janeijkl123May-19137.00%11Q4
Mary Janeijkl123Jun-19130.00%12Q4
John Smithefgh123Jul-18107.40%1Q1
John Smithefgh123Aug-18120.00%2Q1
John Smithefgh123Sep-18126.21%3Q1
John Smithefgh123Oct-18120.00%4Q2
John Smithefgh123Nov-18110.00%5Q2
John Smithefgh123Dec-18120.00%6Q2
John Smithefgh123Jan-19110.00%7Q3
John Smithefgh123Feb-19130.00%8Q3
John Smithefgh123Mar-19110.00%9Q3
John Smithefgh123Apr-19140.00%10Q4
John Smithefgh123May-19137.00%11Q4
John Smithefgh123Jun-19130.00%12Q4
Rodrigo Teixeiraabcd123Jul-18107.40%1Q1
Rodrigo Teixeiraabcd123Aug-18120.00%2Q1
Rodrigo Teixeiraabcd123Sep-18126.21%3Q1
Rodrigo Teixeiraabcd123Oct-18120.00%4Q2
Rodrigo Teixeiraabcd123Nov-18110.00%5Q2
Rodrigo Teixeiraabcd123Dec-18120.00%6Q2
Rodrigo Teixeiraabcd123Jan-19110.00%7Q3
Rodrigo Teixeiraabcd123Feb-19130.00%8Q3
Rodrigo Teixeiraabcd123Mar-19110.00%9Q3
Rodrigo Teixeiraabcd123Apr-19140.00%10Q4
Rodrigo Teixeiraabcd123May-19137.00%11Q4
Rodrigo Teixeiraabcd123Jun-19130.00%12Q4

@parry2k After applying the changes to the other templates I realised that whenever I have two people on the list (The dataset I sent you only had one person), the measures sum up for both users.

 

i.e. see screen shot below:

It picks up the correct % column, however, on the YTD Avg HR column, it is adding up the percentage for user 1 and user 2

Would you have any thoughts? Or are able to replicate the issue on your end?

Capture.PNG

@RodrigoTXRAwe need to update DAX formul to seperate by user, I will get back to you soon.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





It is much appreciated @parry2k it's been great learning new skills with you.

@parry2k unbelievable, fantastic solution. It indeed worked for me. I required to make some changes as my Month column was displaying the date type as text for the month description, so i just went back to the original settings and it worked well.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.