Get last known value

[Anonymous]

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

[AlB · Accepted solution]

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

[Anonymous]

This is truly wonderful. Thank you a lot @AlB 

[AlB]

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.

[Anonymous]

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