Home Forums Power Pivot First date in table

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

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

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    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>

    #15486

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Not sure if this helps display the issue.

    Attachments:
    You must be logged in to view attached files.
    #15488

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    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

    #15489

    brawnystaff
    Participant
    • Started: 8
    • Replies: 22
    • Total: 30

    See attached to get you started…

    Attachments:
    You must be logged in to view attached files.
    #15507

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    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.

    #15512

    brawnystaff
    Participant
    • Started: 8
    • Replies: 22
    • Total: 30

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

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.