Calculate ralted date in 1 to many relatonship based on filtered period

[bsas]

Hi All,

I have problem with calculating related close date in opp table based on selected period from snap table.

There are 2 tables opp:

id	closedate	related date
1111	7/30/2020
2222	7/15/2020

and snap

id	closedate	snap period (end of week)
1111	7/30/2020	7/31/2020
1111	7/17/2020	7/19/2020
1111	7/2/2020	7/5/2020
2222	7/15/2020	7/19/2020
2222	7/4/2020	7/5/2020

relationship is 1 opp to many snap with single cross filter direction.
I need to have related date calculated using calculated column based on selected snap period from snap Table.
e.g. if snap period = 7/5/2020:

id	closedate	related date
1111	7/30/2020	7/2/2020
2222	7/15/2020	7/4/2020

if snap period is 7/19/2020:

id	closedate	related date
1111	7/30/2020	7/17/2020
2222	7/15/2020	7/15/2020

[Icey · Accepted solution]

Hi @bsas ,

The value of each row of calculated column  will not change with the change of the slicer. It is not dynamic. 

It is suggested to create a measure like so:

Related Date Measure =
LOOKUPVALUE (
    snap[closedate],
    snap[id], SELECTEDVALUE ( opp[id] ),
    snap[snap period], SELECTEDVALUE ( snap[snap period] )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.