Calculate new date based on columns in two tables

[PhilippeJosse]

Dear Forum members, 

unfortunately i was unable to find a matching solution, so thought i would go ahead and post the challange i am confronted with and hoping to find support.

--->

I have a Table A with 36.000 rows, and it grows about 300 rows a month. 

ID	Date	Category	Target Date
1	01.02.2024	HV	..
2	02.01.2024	STD	..
3	02.01.2024	HV	..

*dd.MM.yyyy

And Table B, which is a "calendar" table.

Date	Year	Quarter	Month	Week	Weekday	Indicator 1	Indicator 2
01.01.2024	2024	Q1	01-Jan	1	1	Yes	Yes
02.01.2024	2024	Q1	01-Jan	1	2	Yes	No
03.01.2024	2024	Q1	01-Jan	1	3	No	No

*dd.MM.yyyy

Would like to accomplish to calculate a new value in column Target Date in Table A for each individual row.

Where the starting value is the value Column Date for respective row.

Next, If column Category = HV then Date+2 (days) else Date+1 (day)

now, using the calculated date as reference, I would like to find the next (or first date higher than the calculated) date in column Date of Table B where a column Indicator 1= "yes" and Indicator 2 = "no".

Appriciate any guidance!

Thank 

Philippe

[rajendraongole1 · Accepted solution]

Hi @PhilippeJosse - Hope in your tables Date columns is date type

create a calculated column in first table, as below:

InitialTargetDate =

IF (

    TABA1[Category] = "HV",

    TABA1[Date] + 2,

    TABA1[Date] + 1

)

another calclated column:

TargetDate =

VAR InitialDate =

    IF (

        TABA1[Category] = "HV",

        TABA1[Date] + 2,

        TABA1[Date] + 1

    )

RETURN

    CALCULATE (

        MIN (TABA2[Date]),

        FILTER (

            TABA2,

            TABA2[Date] > InitialDate &&

            TABA2[Indicator 1] = "Yes" &&

            TABA2[Indicator 2] = "No"

        )

    )

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!