How to lookup a value from a source with no time

Chmacpherson

Context

1. My Destination module has the following dimensions: (1) Employee ID (2) Line Items (3) Time
2. My Source module has the following dimensions: (1) Movement Code (2) Line Items
   • Employee ID is a line item (List)
   • Effective Date is a line item (Date)

Goal

1. In the destination module, populate an employee's job level on the effective date based on the Source module.

Challenge

1. Source module has the effective date as a line item and not a dimension, so I am struggling to make the connection between the source and the destination.
   • Destination module (time as dimension)
   • Source module (time as line item)

Any guidance on this would be greatly appreciated!

 

Many thanks,

Chad

TonyViGrand

Hi @Chmacpherson 

 

I assume the timescale in the destination module is "Day", if that is the case then you can try the formula as  
Data from source[Firstnonblank:Employee ID,firstnonblank:EffectiveDate] .

If the timescale is month in destination, then first create a line item example "EffectiveMonth" in source module to convert the date into month using the Period formula and then use the above formula with instead of EffectiveDate line item in firstnonblank, you could use EffectiveMonth line item.

 

Thanks

Tony