Home Forums Power Pivot Last Location Worked

Tagged: ,

This topic contains 6 replies, has 2 voices, and was last updated by  sjhc1177 1 month, 3 weeks ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #18119

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

    H, I have data in power query and then loaded in power pivot.

    I would like to know the last location you worked at.

    So each employee has a line with their pay amount, location, date…

    They can move from location to location so I need to know MAX “Start of Pay” and then the location from that pay period.

     

    This gives me LAST DAY WORKED. I just need to now know the location.

    LAST DATE WORKED:=CALCULATE(MAX(RISEPayrollHistoryCombined[Start Of Pay Period]))

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

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

    See below

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

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

    Thanks, I think that did it.

     

    I did try every way I could think of, was close but just couldn’t finish.

    #18124

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

    Hi, thanks for your help.

    Last question I have is now I want to be able to subtotal by department. But since it’s a measure I can’t and can’t create a slicer from it either.

     

    What are your thoughts?

    #18125

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

    Would need to created a Calculated Column for use in a slicer.

    See attached revision..

     

     

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

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

    Wow, that is great. Thank you so much.

    I can see the application of using that in other areas.

    You have helped me a ton.

     

    Cheers

    #18136

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

    Hi, I’ve been applying the suggestion given above to a few situations. But now I have a curve ball so to speak.

    I would like to know the last discount given to an item. But the code above is looking for things based on a sorting logic.

    But see below my issue, see attached image

    This is the calculated column formula.

    =
    CALCULATE (
    LASTNONBLANK (tblInvPriceDisc[disc_code], 1 ),
    TOPN (
    1,
    FILTER (
    tblInvSkuMaster,
    tblInvSkuMaster[SKU]
    = EARLIER (tblInvSkuMaster[SKU] )
    ),
    LASTDATE ( tblInvPriceDisc[start_active_dt] )
    )
    )

    The correct amount should be ($20N010919), but this is what is chosen ($30N071818)

    Attachments:
    You must be logged in to view attached files.
Viewing 7 posts - 1 through 7 (of 7 total)

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