Obtain date from different table

[Lumegu]

Dear community,

I have the following issue for which I hope one of you could perhaps help me out:

There are two data sets:

1. Contains items:

Item	Status item	Due date activity B (to be calculated)
Item 1	In progress	1-12-2021
Item 2	In progress	13-12-2022

2. Contains activities to these items

Item	Activity	Status activity	Due date activity
Item 1	A	In progress	1-1-2022
Item 1	B	In progress	1-12-2021
Item 1	C	In progress	1-10-2021
Item 1	D	In progress	2-12-2021
Item 1	E	Done	3-12-2021
Item 2	A	In progress	1-1-2022
Item 2	B	Done	13-12-2022
Item 2	C	Done	1-10-2021

I would like to obtain the due date for activity B (dataset activities) for each item in the items dataset (Due date activity B (to be calculated)). I was wondering how to formulate this (vlookup type of calc) into a DAX formula?

[VahidDM]

Hi @Lumegu 

If you want to add a column to your table 1, try this code to add a calculated column:

Due date activity B =
CALCULATE (
    MAX ( 'Table 2'[Due date activity] ),
    FILTER (
        'Table 2',
        'Table 2'[Activity] = "B"
            && 'Table 2'[Item] = EARLIER ( 'Table'[Item] )
    )
)

output:

if you want a measure, use @smpa01  solution.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[smpa01 · Accepted solution]

@Lumegu  try this measure

Measure = CALCULATE(MAX('Table 2'[Due date activity]),FILTER('Table 2','Table 2'[Activity]="B"))