DAX formula lookup

[Anonymous]

Hi, I am very new to DAX ( I have worked some with access formulas so its a little familiar)

I have a column with the expense date. I need help with a dax formula to look up the date in the expense data to a table that contains the payroll run dates  and return the payroll run information. 

The second part of the formula is there are many payroll runs ( such as Domestic , International) So I create an ID for the payroll type too.

Table 1-   EXPENSE DATA

   ID                                     Expense date      Payroll Batch ( lookup formula expected answer)

   DOMESTIC                       01/01/2022             1

   International                    01/30/2022             I2

Table 2 -PAYBATCH ( lookup table)

   ID                       Start Date     End Date    Payroll batch

Domestic              01/01/22       01/15/22      1

Domestic              01/16/22       01/31/22      2

International        01/05/22        01/20/22     I1

International       01/21/22         02/05/22     I2

Any help is appreciated. I could not even come close to getting this to work. Thank you so much, I really would like to write this in BI/DAX as it is so much more powerful. I do have it working in excel , but its clunky and it seems to be unstable and breaks.

[Anonymous]

thank you I will give this a shot.. this looks correct.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a calculated column.

Payroll Batch CC =
VAR currentID = Expense[ID]
VAR paybatch_table_filter =
FILTER (
Paybatch,
Paybatch[ID] = currentID
&& Paybatch[Start Date] <= Expense[Expense date]
&& Paybatch[End Date] >= Expense[Expense date]
)
RETURN
SUMMARIZE ( paybatch_table_filter, Paybatch[Payroll batch] )