Tagged: Help please
October 11, 2017 at 7:16 pm #8419
I have a table with a bunch of people’s hourly earnings.
I have created a ranking of the max rate that people have for the month, and also for the prior month.
I would like to create a ranking of the individual across all prior months.
I have the following metrics:
Max Rate = Max(EmpTrans[HourlyRate])
Rate Rank = RANKX(ALL(EmpTrans[Emp#],EmpTrans[FullName]),[Max Rate])
Prev Month Rank = CALCULATE([Rate_Rank],
But I realized what I want is the persons all time rank excluding the current Month. Or perhaps just the max rate of all months previous to this month.
Help please.October 11, 2017 at 8:09 pm #8421
What is the name of the column used in EmpTrans that relates to the Date column in the Calendar table?
And what are you using to analyze your data? Power BI or Excel Power Pivot? If Excel, what version: 2010, 2013 or 2016?
Can you put up some sample data that where you know the correct answers? Data does not have to be real data, but the structure of your tables should be consistent with the information provided so far:
Calendar table, with (at least) a column for Date (does your calendar table have a column indicating the date belongs to the current month? if so, please include it because it will most likely be part of a formula).
EmpTrans table, with columns for Hourly Rate, Emp#, Full Name, and what-ever-the-date-column-is-named.
Looking forward to your response.
TomOctober 12, 2017 at 12:56 pm #8426
Power Pivot on Excel Office 365 Pro Plus (2016 – 64 Bit)?
Calendar : this is dynamically built of the [Pay Period End] dates in the EMPTrans Table
Date | Year-Month | Year | Month | Day ….
… Starts with earliest Pay Period End Date in the trans table
09/01/2017 | 2017-09 | 2017 | 09 | 01
09/02/2017 | 2017-09 | 2017 | 09 | 02
10/01/2017 | 2017-10 | 2017 | 10 | 01
EMP# | Fullname | RateCode | Rate | Hours | Gross | Pay Period End |
1234 | Gerry Geyser | ST | 29 | 30 | 957.00 | 07/07/2017
1234 | Gerry Geyser | ST | 25 | 40 | 1320.00 | 08/25/2017
1234 | Gerry Geyser | ST | 25 | 22 | 550.00 | 09/01/2017
1234 | Gerry Geyser | OT | 37.5 | 10 | 375.00 | 09/01/2017
1234 | Gerry Geyser | ST | 25 | 20 | 500.00 | 09/08/2017
2345 | Bobby Bones | ST | 22 | 35 | 770.00 | 06/23/2017
2345 | Bobby Bones | ST | 22 | 35 | 770.00 | 08/25/2017
2345 | Bobby Bones| ST | 30 | 36 |1080.00 | 09/01/2017
2345 | Bobby Bones | OT | 45 | 8 | 360.00 | 09/01/2017
2345 | Bobby Bones | ST | 30 | 20 | 600.00 | 09/08/2017
In September Gerry’s best Straight rate (ST) was 25$ ( Ranks 2nd)
In August it was also 25$ ( Ranks 2nd)
Previously his best rate previously was 29$ (Ranks 1st)
In September Bobby’s best Straight rate (ST) was 30$ (Ranks 1st)
In August it was 22$ ( Ranks 2nd)
Previously his best rate previously was also 22$ (Ranks 2nd)
On a pivot table I have put Max rate, Rate Rank, the previous month rank and fingers crossed a Previous All time rank.
Using slicers I have selected just: September and Straight time (ST)
EMP# | Max Rate | Rate Rank | Previous Month Rank | *Previous All Time Rank
1234 | 25 | 2 | 2 | *1
2345 | 30 | 1 | 2 | *2
* this is the measure I don’t know how to define.
Hopefully this all made sense.
I would like to know the rank of the persons highest rate defined prior to the current month. I wish to identify people that made significant jumps and I would like more than just the last month rank.
Any attempts are greatly appreciated so thanks in advance.
[Edit Grammer and a couple rates to make it clear I don’t want the Previous all time rank to consider the current month.]October 12, 2017 at 1:04 pm #8427
uhg 1 edit and I spell Grammar wrong.October 12, 2017 at 2:03 pm #8428
Have just downloaded data and imported into an Excel 2016 workbook.
Don’t worry about the spelling, it’s the numbers and ideas that are important here.
Will get back to you shortly.
TomOctober 12, 2017 at 3:47 pm #8429
I noticed I made a mistake in the results description of Gerry’s Previous month rank, it should be 1st as 25 is larger than 22. and certainly in this case they both shouldn’t be 2nd.October 13, 2017 at 2:37 am #8432
Because there is not an Employees table, I resorted to a trick: first creating a calculated column for the max rate of all time for each employee (excluding the “current” month). Also created a calculated column for the rank of max rate of all time (excluding “current” month). The values repeat, but it is always the same result for the same employee.
The actual measures were then created using a MAX with the calculated columns (although I could have chosen MIN or AVERAGE and the result for the line-items rows would have been the same).
It would have been easier with a table for employees.
TomOctober 16, 2017 at 5:08 pm #8455
I have been trying to implement your version slightly different and am striking out. I need the Max Rate excluding “current” to be dynamic to the current month selected so I think the calculated column is sinking me.
I will keep trying. I’ve attached an updated version of yours with a bit more data to illustrate. ie if you select July Bobby should get top spot in the prior months.
I also added the employees table based on your comment.
Kudos, to Tom for his effort and any others that might be up to the challenge.October 16, 2017 at 5:16 pm #8456
AttachmentOctober 16, 2017 at 9:29 pm #8463
Require Employee table columns to be on pivot rows.October 17, 2017 at 12:59 pm #8470
Thank you so much Tom, you’re a Rockstar!
In one iteration I had gotten as far as the Month start column but threw it away because I couldn’t figure out how to wedge it into the calculate filter and I thought I was going down the wrong path.
When I saw the MIN function (in dynamic month start) I almost thought you had gone down the disconnected slicer path.
Finally the light bulb went off. This is so beautiful and is way more functional as it will adjust to other time frames ie quarters or years.
I don’t see Kudos point’s in this forum but you earned it in my book.
Thanks again.October 17, 2017 at 1:17 pm #8471
I setup my calendar columns in the power Query import, and when I tried to use the Date.EndOfMonth Function with the optional , -1 for the previous month it errored. Because I also had a day field I used, “=Date.AddDays([Date], -1*[Day]+1)” for my start of month calculation.October 17, 2017 at 3:04 pm #8475
I believe the Power Query Date.EndOfMonth and Date.StartOfMonth do not take a second value, such as -1, but the DAX function EOMONTH does.
You must be logged in to reply to this topic.