Home › Forums › Power Pivot › Last Location Worked
Tagged: dax, Power Query
This topic contains 6 replies, has 2 voices, and was last updated by sjhc1177 4 years, 6 months ago.
-
AuthorPosts
-
August 12, 2019 at 10:42 pm #18119
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.August 12, 2019 at 11:58 pm #18121See below
Attachments:
You must be logged in to view attached files.August 13, 2019 at 12:27 am #18123Thanks, I think that did it.
I did try every way I could think of, was close but just couldn’t finish.
August 13, 2019 at 2:57 pm #18124Hi, 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?
August 14, 2019 at 12:41 am #18125Would need to created a Calculated Column for use in a slicer.
See attached revision..
Attachments:
You must be logged in to view attached files.August 14, 2019 at 2:12 am #18127Wow, 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
August 26, 2019 at 2:54 pm #18136Hi, 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. -
AuthorPosts
The forum ‘Power Pivot’ is closed to new topics and replies.