How do I get a value from another table based on a date range

[EaglesTony]

Hi,

  I have a table with multiple Date Ranges (table called "DateRanges"):

Column1    Cycle   CycleStartDate   CycleEndDate

2                 1          3/31/2022          4/13/2022

2                 2          4/15/2022          4/27/2022

2                 3          4/28/2022          5/11/2022

2                 4          5/12/2022          5/25/2022

   I have another table that has a date and I need to get the Cycle from the first table:

Key                Date

1                    4/20/2022

2                    5/25/2022

I would want another field, so it looks like:

Key                Date               Cycle

1                    4/20/2022      2

2                    5/25/2022      4

[divyed]

Hello @EaglesTony ,

Here is the DAX for you assuming T_1 and T_2 are first and Second tables respectively with data given :

NewTable =

ADDCOLUMNS(

    T_2,

    "Value",

    CALCULATE(

        MAX(T_1[Cycle]),  -- Use MAX to get the value from T_1 (could be SUM, MIN, etc. depending on your needs)

        FILTER(

            T_1,

            T_1[StartDate] <= T_2[Date] && T_1[EndDate] >= T_2[Date]

        )

    )

)

Please mark this as solution if this has solved your problem. 

Cheers

[EaglesTony]

What is "New Table" ?...A column or a measure on a certain table ?

[divyed]

A new table .

Do you need a measure ?

[divyed]

Hello @EaglesTony ,

If you want column added in second table, here is the code :

Cycle1 =

CALCULATE(

    MAX(T_1[Cycle]),  -- You can use SUM, MIN, etc. as needed

    FILTER(

        T_1,

        T_1[StartDate] <= T_2[Date] && T_1[EndDate] >= T_2[Date]

    )

)

Please mark this as solution if this has solved your problem. 

[EaglesTony]

I'm able to get the correct cycle on this table.(call it FinalTable)

My issue now is there is a 3rd table (ReportTable), which I need to get this field into, but the issue is that the 3rd table has Keys that some don't match the FinalTable.

[divyed]

Thanks for confirming first one is completed. Can you please add sample data for 3rd table ?

[EaglesTony]

FinalTable has:

Key                Column1        Column2   Cycle

1                    John                Doe          2 

3                    Jane                 Doe          null

ReportTable has

Key                Column1a        Column2a  

1                    John                Doe           

5                    Alex                 Doe          

I'd like to have ReportTable as

Key                Column1a        Column2a  Cycle

1                    John                Doe              2

5                    Alex                 Doe             null

[divyed · Accepted solution]

Hello @EaglesTony ,

Simple use Lookup between FinalTable and ReportTable to get the value :

Cycle =

LOOKUPVALUE(

    FinalTable [Cycle],        -- Column to return

    FinalTable [Key], ReportTable [Key] -- Matching Key from both tables

)

I have checked this with sample data :

I hope this is what you are looking for .

Did I solve your Query ? Please mark this as solution. Appreciate Kudos always

Cheers

[EaglesTony]

The problem I have now is, that I use cycle in a slicer and when i try to add "Both" in the relationship between these 2 tables, it says it is a circular dependency(I assume since both values are calculated) ?????

[divyed]

Hello @EaglesTony ,

Let's make it more clear. You have now 2 tables 

1. Final Table with columns Key ,Date and Cycle

2. ReportTable with Key and other columns

3. A mesure for Cycle

You want to show values from FinalTable and ReportTable and Cycle as a slicer.

Have you checked if there is relationship setup between FinalTable and ReportTable ? If not, create a relationship on Key and try again.

I am able to use cycle as slicer to show values from both the tables (kindly ignore blank value , this is for testing only).

I hope this helps.

[EaglesTony]

What i have is:

1) FinalTable with Key and Cycle

2) ReportTable that has Key and Cycle (based off a lookup to FinalTable)

3) Slicer that is using a Cycle field from a Cycle table

What I am trying to do is build a relationship between Cycle of the ReportTable to Cycle of the Cycle table, and have it as "Both", so that when the Slicer changes(Say Cycle 2), it will show all those records in ReportTable that have Cycle 2.

However when trying to establish this relationship it is giving me a circular reference.

[EaglesTony]

I assume this is a measure on the ReportTable ?

[divyed]

Hello @EaglesTony ,

No, this was adding column to 3rd table. To convert into measure please change as per below dax :

// Please change table names from T_2 to FinalTable  and T_3 to ReportTable

MeasureValue =
CALCULATE(
MAX(T_2[Cycle]), -
T_2[Key] = SELECTEDVALUE(T_3[Key]) -- Matching the Key from T_2 and T_3
)

Cheers 

[divyed]

Hello @EaglesTony 

If you want a measure , use below dax:

Cycle =

CALCULATE(

    MAX(T_1[Cycle]),  

    FILTER(

        T_1,

        T_1[StartDate] <= MAX(T_2[Date]) &&

        T_1[EndDate] >= MAX(T_2[Date])

    )

)

Please mark this as solution if this has solved your problem. 

[NHarington]

Hello, 

Use the following DAX formula to create the calculated column that returns the correct cycle from the DateRanges table:

Cycle = 
CALCULATE(
    MAX(DateRanges[Cycle]),
    FILTER(
        DateRanges,
        Transactions[Date] >= DateRanges[CycleStartDate] &&
        Transactions[Date] <= DateRanges[CycleEndDate]
    )
)

This method should work for retrieving the correct cycle based on the date ranges in Power BI.

If this works for you please mark my answer as your solution.

Thank you!

[EaglesTony]

Is there a way to do this in Power Query instead ?

[NHarington]

1. 

Load both DateRanges and the second table (with Key and Date) into Power Query.
2. 

Select the Transactions table.
Go to the Add Column tab and select Custom Column.
Write a custom column to check if the date in the Transactions table falls between the CycleStartDate and CycleEndDate in the DateRanges table.

You can write this in a Custom Column:

List.Select(
    DateRanges,
    each [CycleStartDate] <= [Date] and [CycleEndDate] >= [Date]
)

This will return all matching rows from DateRanges. You’ll want to expand that column to include the Cycle value.

After creating the custom column, expand the new column (click on the two arrows next to the column name) and select only the Cycle column from the DateRanges table.
Now your Transactions table will have the Cycle values based on the date range.

3.
Now that your Transactions table has the Cycle, you can merge it with Table1 based on the Key column.

Select Table1 in Power Query.
Click on Home > Merge Queries.
Choose Table1 as the first table and Transactions as the second table.
Select the Key column from both tables to perform the merge.
Use a Left Outer Join so that all rows from Table1 are retained.
After the merge, expand the Cycle column from the Transactions table.

4.

After the merge, Table1 will now contain a Cycle column with the corresponding value from Transactions, based on the Key and Date.
If there’s no matching Key in Transactions, the Cycle will be null.

[EaglesTony]

For Step #2, how can I reference the fields in DateRange table, as the only fields available are in the Transactions table ?

[EaglesTony]

I like this approach and seems to work.

How now do I get this Cycle value to another table in DAX both tables have Key as a column.

If I now have the following in a table called Table2:

Key                Date               Cycle

1                    4/20/2022      2

2                    5/25/2022      4

I have table1 with(notice no Key 2):

Key                Column1        Column2

1                    John                Doe

3                    Jane                 Doe

I need table1 to be:

Key                Column1        Column2   Cycle

1                    John                Doe          2 

3                    Jane                 Doe          null

[NHarington]

Hello,

To bring the Cycle value from Table2 into Table1 you can create a calculated column in Table1 that looks up the corresponding Cycle from Table2 using the common Key column.

Create a Relationship:
Ensure that there is a relationship between Table1 and Table2 using the Key column. If the relationship doesn’t already exist, go to the Model view and create a one-to-many relationship from Table1[Key] to Table2[Key].

Create a Calculated Column in Table1:
Go to Modeling and select New column.
Write the following DAX formula to create a calculated column that brings in the Cycle value from Table2:

Cycle = RELATED(Table2[Cycle])

The RELATED function is used to fetch a value from a related table. In this case, it retrieves the Cycle from Table2 where the Key matches.
Result:

After applying the formula, Table1 will now have a new Cycle column that contains the matching Cycle from Table2 for each Key. If there is no matching Key, it will return null.

Let me know if you have any more questions!