Lookup value for date with 31 days

[powerbi_9]

Hi,

I am fairly new to Power BI and trying to understand the DAX funtions. I am struck with the below. Any help is appreciated.

The request is to get the first status based on the start date in dataset2 where parent id matches and start date of data set2 is with in 31 days of startdate of dataset1

[powerbi_9]

Also, it is possible that both datasets may have the duplicate parent id's.

[v-cherch-msft · Accepted solution]

Hi @powerbi_9

You may refer to below dax to create the calculated columns and link the two table in relationship view.

date_outcome =
CALCULATE (
    FIRSTNONBLANK ( Table2[StartDate], 1 ),
    FILTER (
        Table2,
        Table2[StartDate] >= Table1[StartDate]
            && Table2[ParentID] = Table1[ParentID]
            && Table2[StartDate]
            < Table1[StartDate] + 31
    )
)

Status_lookup =
CALCULATE (
    FIRSTNONBLANK ( Table2[Status], 1 ),
    TOPN (
        1,
        FILTER (
            Table2,
            Table2[StartDate] = Table1[date_outcome]
                && Table2[ParentID] = Table1[ParentID]
        ),
        Table2[ChildID], ASC
    )
)

 

Regards,

Cherie

[powerbi_9]

Worked perfectly. Thank you!