Forum Discussion

powerbi_9's avatar
powerbi_9
Frequent Visitor
8 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 in dataset2 where parent id matches and start date of data set2 is with in 31 days of startdate of dataset1

  • v-cherch-msft's avatar
    v-cherch-msft
    8 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

3 Replies

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

        Worked perfectly. Thank you!