cancel
Showing results for
Did you mean:
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 screen shots

1 ACCEPTED SOLUTION
Super User

@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

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

11 REPLIES 11
Super User

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

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

Helper I

Hi Parry

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 Name xxxxxxxxxxxxx Jul-18 100.00% 100.00% 1 Q1 Full Name xxxxxxxxxxxxx Aug-18 120.00% 110.00% 2 Q1 Full Name xxxxxxxxxxxxx Sep-18 126.21% 115.40% 3 Q1 Full Name xxxxxxxxxxxxx Oct-18 120.00% 116.55% 4 Q2 Full Name xxxxxxxxxxxxx Nov-18 110.00% 115.24% 5 Q2 Full Name xxxxxxxxxxxxx Dec-18 120.00% 116.04% 6 Q2 Full Name xxxxxxxxxxxxx Jan-19 110.00% 115.17% 7 Q3 Full Name xxxxxxxxxxxxx Feb-19 130.00% 117.03% 8 Q3 Full Name xxxxxxxxxxxxx Mar-19 110.00% 116.25% 9 Q3 Full Name xxxxxxxxxxxxx Apr-19 140.00% 118.62% 10 Q4 Full Name xxxxxxxxxxxxx May-19 137.00% 120.29% 11 Q4 Full Name xxxxxxxxxxxxx Jun-19 130.00% 121.10% 12 Q4
Super User

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

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

Super User

Is your YTD definition is Jan - Dec or different?

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

Helper I

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

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

Super User

@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

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

Helper I

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 Rep User ID FY Appraisal Month Appraisal Month % Sort Quarter Mary Jane ijkl123 Jul-18 107.40% 1 Q1 Mary Jane ijkl123 Aug-18 120.00% 2 Q1 Mary Jane ijkl123 Sep-18 126.21% 3 Q1 Mary Jane ijkl123 Oct-18 120.00% 4 Q2 Mary Jane ijkl123 Nov-18 110.00% 5 Q2 Mary Jane ijkl123 Dec-18 120.00% 6 Q2 Mary Jane ijkl123 Jan-19 110.00% 7 Q3 Mary Jane ijkl123 Feb-19 130.00% 8 Q3 Mary Jane ijkl123 Mar-19 110.00% 9 Q3 Mary Jane ijkl123 Apr-19 140.00% 10 Q4 Mary Jane ijkl123 May-19 137.00% 11 Q4 Mary Jane ijkl123 Jun-19 130.00% 12 Q4 John Smith efgh123 Jul-18 107.40% 1 Q1 John Smith efgh123 Aug-18 120.00% 2 Q1 John Smith efgh123 Sep-18 126.21% 3 Q1 John Smith efgh123 Oct-18 120.00% 4 Q2 John Smith efgh123 Nov-18 110.00% 5 Q2 John Smith efgh123 Dec-18 120.00% 6 Q2 John Smith efgh123 Jan-19 110.00% 7 Q3 John Smith efgh123 Feb-19 130.00% 8 Q3 John Smith efgh123 Mar-19 110.00% 9 Q3 John Smith efgh123 Apr-19 140.00% 10 Q4 John Smith efgh123 May-19 137.00% 11 Q4 John Smith efgh123 Jun-19 130.00% 12 Q4 Rodrigo Teixeira abcd123 Jul-18 107.40% 1 Q1 Rodrigo Teixeira abcd123 Aug-18 120.00% 2 Q1 Rodrigo Teixeira abcd123 Sep-18 126.21% 3 Q1 Rodrigo Teixeira abcd123 Oct-18 120.00% 4 Q2 Rodrigo Teixeira abcd123 Nov-18 110.00% 5 Q2 Rodrigo Teixeira abcd123 Dec-18 120.00% 6 Q2 Rodrigo Teixeira abcd123 Jan-19 110.00% 7 Q3 Rodrigo Teixeira abcd123 Feb-19 130.00% 8 Q3 Rodrigo Teixeira abcd123 Mar-19 110.00% 9 Q3 Rodrigo Teixeira abcd123 Apr-19 140.00% 10 Q4 Rodrigo Teixeira abcd123 May-19 137.00% 11 Q4 Rodrigo Teixeira abcd123 Jun-19 130.00% 12 Q4
Helper I

@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?

Super User

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

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

Helper I

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

Helper I

@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.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!