Forum Discussion

powerbi_9's avatar
powerbi_9
Frequent Visitor
7 years ago
Solved

Lookup value for date with 31 days

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 ...
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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's avatar
powerbi_9
Frequent Visitor
7 years ago

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

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      powerbi_9
      Frequent Visitor
      7 years ago

      Worked perfectly. Thank you!