Home › Forums › Power Pivot › First date in table
Tagged: poweerpivot; max
This topic contains 5 replies, has 2 voices, and was last updated by brawnystaff 5 years, 4 months ago.
-
AuthorPosts
-
April 11, 2019 at 6:17 pm #15485
Hi, I have a payroll table. And I would like to know when an employee last received an increase.
I’ve pasted below sample data. The hourly rate changed Mar 11/19.
<table width=”1619″>
<tbody>
<tr>
<td width=”93″>First Name</td>
<td width=”118″>Department Code</td>
<td width=”124″>Department Name</td>
<td width=”77″>Home Dept</td>
<td width=”48″>Hourly Rate</td>
<td width=”62″>Day – Effective Date</td>
<td width=”85″>Month – Effective Date</td>
<td width=”74″>Year – Effective Date</td>
<td width=”70″>Salary Rate</td>
<td width=”83″>HourlyPay</td>
<td width=”64″>OvertimePay</td>
<td width=”83″>Commission</td>
<td width=”64″>Bonus</td>
<td width=”56″>AccruedVacPaid</td>
<td width=”64″>Retro</td>
<td width=”97″>SickTimeTaken</td>
<td width=”116″>StatHolidayPay</td>
<td width=”96″>StatWorked</td>
<td width=”64″>Run</td>
<td width=”81″>Payroll Year</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>13</td>
<td style=”font-weight: 400;”>8</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>40.25</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>18</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>27</td>
<td style=”font-weight: 400;”>8</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>39.91</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>19</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>10</td>
<td style=”font-weight: 400;”>9</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>35.75</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>32.93</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>20</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>24</td>
<td style=”font-weight: 400;”>9</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>44.42</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>28.04</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>21</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>8</td>
<td style=”font-weight: 400;”>10</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>35.59</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>50.29</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>22</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>22</td>
<td style=”font-weight: 400;”>10</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>42.59</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>51.25</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>23</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>5</td>
<td style=”font-weight: 400;”>11</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>43.82</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>98.37</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>24</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>19</td>
<td style=”font-weight: 400;”>11</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>62.44</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>115.36</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>25</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>3</td>
<td style=”font-weight: 400;”>12</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>45.25</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>11.97</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>222.57</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>26</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>17</td>
<td style=”font-weight: 400;”>12</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>46.56</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>128.67</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>9.17</td>
<td style=”font-weight: 400;”>1</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>31</td>
<td style=”font-weight: 400;”>12</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>55.16</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>10.29</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>2</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>1</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>54.08</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>87.8</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>3</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>28</td>
<td style=”font-weight: 400;”>1</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>37.17</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>4</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>11</td>
<td style=”font-weight: 400;”>2</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>43.25</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>128.18</td>
<td style=”font-weight: 400;”>33.94</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>5</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>25</td>
<td style=”font-weight: 400;”>2</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>55.18</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>10.55</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>6</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>15</td>
<td style=”font-weight: 400;”>11</td>
<td style=”font-weight: 400;”>3</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>58.67</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>56.13</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>7</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>15</td>
<td style=”font-weight: 400;”>25</td>
<td style=”font-weight: 400;”>3</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>57.08</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>133.66</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>8</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
</tbody>
</table>April 11, 2019 at 6:22 pm #15486Not sure if this helps display the issue.
Attachments:
You must be logged in to view attached files.April 11, 2019 at 7:09 pm #15488Hi, additional thought.
It would be even better to see all the pay rates for each employee Ranked.
To see each date we provided you an increase.
thanks
April 11, 2019 at 8:15 pm #15489See attached to get you started…
Attachments:
You must be logged in to view attached files.April 17, 2019 at 2:32 am #15507Thanks for the post.
That did the trick.
Can you explain the FIRSTNONBLANK LINE?
I have a pitvottable I created with the sales results. I have some transactions where an item was exchanged. So the value of the net transaction is zero. But it still counts as a transaction and therefore the sales associate is in my list. I would like to filter on transactions <>0.
But you can’t filter measures. IE a months worth of sales for you equal 0 at location 1. So I don’t need you listed. But in location 2 you have sales of $100. So you should be listed.
How to I get the list to only list non zero NET transactions.
April 17, 2019 at 5:29 pm #15512Regarding explaining the FIRSTNONBLANK line, the measure is similar to a nested formula in that TOPN operator is determining the top maximum value based on the criteria, the FIRSTNONBLANK operator is just ensuring the returned value is not a blank value.
Regarding your second issue with not counting 0 values, if summing values, typically if summing a field 0’s would not be counted. However, if doing a count, if would affect your results. Would need you to upload a sample Excel workbook with sample data and expected results..
-
AuthorPosts
The forum ‘Power Pivot’ is closed to new topics and replies.