Home › Forums › Power Pivot › First date in table

Tagged: 

This topic contains 5 replies, has 2 voices, and was last updated by  brawnystaff 5 years ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • April 11, 2019 at 6:17 pm #15485

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    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 #15486

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    Not sure if this helps display the issue.

    Attachments:
    You must be logged in to view attached files.
    April 11, 2019 at 7:09 pm #15488

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    Hi, 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 #15489

    brawnystaff
    Participant
    • Started: 0
    • Replies: 4
    • Total: 4

    See attached to get you started…

    Attachments:
    You must be logged in to view attached files.
    April 17, 2019 at 2:32 am #15507

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    Thanks 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 #15512

    brawnystaff
    Participant
    • Started: 0
    • Replies: 4
    • Total: 4

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

  • Author
    Posts
Viewing 6 posts - 1 through 6 (of 6 total)

The forum ‘Power Pivot’ is closed to new topics and replies.