Forum Discussion
Get last known value
Hi all,
i'm trying to get the last know value from a table, but can't figure out the steps to do it.
The goal is to have a name for each role. So if a name isn't entered in the yearmonth, it should fall back to the previous month and retrieve it. So when i filter on yearmonth = 2021 05, it should show the green values and all values that aren't available should come from 2021 04.
What have i tried so far: created a temp table which stores all roles and crossjoined it, but that didn't turn out what i was hoping for. I searched this forum on "get last known value", but couldn't find something that's similar to this problem. So i'm hoping you can point me in the right direction.
Tnx in advance
Anonymous
Not sure I understand. Do you want this in a visualor in PQ?
Why are there two entries for Asset manager in 2021 05? I've eliminated the last one.
Check the attached file for a possible solution
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
4 Replies
- AlB
Community Champion
Hi Anonymous
1. Where is the list with all the roles that are to be shown?
2. Can you share the sample data above in text-tabular format so that the contexts can be copied. Just paste the contents of the Excel, PBI table here.
3. Show the exact expected result based on that sample data
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi AlB ,
1. These are all the roles:
Assetmanager
Productiemanager OB
Locatiemanager regio
Maintenance Engineer
Reliabilty Engineer
Business controller
Vlootsupport
Productie Engineer
Supply Chain Management
Reiniging
2. can't seem to copy/past an excel:
Role Yearmonth Name
Assetmanager 2021 04 AA
Productiemanager OB 2021 04 BB
Locatiemanager regio 2021 04 CC
Maintenance Engineer 2021 04 DD
Reliabilty Engineer 2021 04 EE
Business controller 2021 04 FF
Vlootsupport 2021 04 GG
Productie Engineer 2021 04 HH
Supply Chain Management 2021 04 II
Reiniging 2021 04 JJ
Assetmanager 2021 05 AAA
Productiemanager OB 2021 05 BBB
Locatiemanager regio 2021 05 CCC
Maintenance Engineer 2021 05 DDD
Reliabilty Engineer 2021 05 EEE
Assetmanager 2021 05 AAAA
3. This is the expected result
- AlB
Anonymous
Not sure I understand. Do you want this in a visualor in PQ?
Why are there two entries for Asset manager in 2021 05? I've eliminated the last one.
Check the attached file for a possible solution
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
This is truly wonderful. Thank you a lot AlB