Create calculated column in Calendar table from range of dates in lookup table.

[saffronbows]

Hello, first post here. Please let me know how I might make my problem easy to reproduce. I am working in Power Query.

I would like to create a calculated column in a basic Calendar table from range of dates in my Lookup table. I want the column to be the related Lookup.[YRTR] value where [Calendar.Date] >= [Lookup.BEGIN_DATE] and [Calendar.Date] < [Lookup.Lead.BEGIN_DATE].

My lookup table has a couple hundred ordered values, each with a unique BEGIN_DATE. To this lookup table I have added Lead.BEGIN_DATE to be the BEGIN_DATE of the subsequent record. Here is a snippet:

YRTR	BEGIN_DATE	Lead.BEGIN_DATE
20225	1/9/2023	5/30/2023
20233	8/22/2022	1/9/2023

Thank you

[Anonymous]

Hi @saffronbows ,

Please try like:

(x) => Table.SelectRows(Lookup,each x[Date] >= [BEGIN_DATE] and x[Date] < [Lead.BEGIN_DATE])[YRTR]

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[saffronbows]

Thank you, Gao. Only a single value should be returned from the lookup table. When I try to expand the list I get "Expression.Error: A cyclic reference was encountered during evaluation." Am I interpreting your syntax correctly? From the query editor, my step looks like "= Table.AddColumn(#"Previous Step", "YRTR", each (Table.SelectRows('Lookup', each 'Calendar'[Date] >= [BEGIN_DATE] and 'Calendar'[Date] < [Lead.BEGIN_DATE])[YRTR]))"

[lbendlin]

Note that a "calculated column" is not something you do in Power Query, it is a DAX thing.

In Power Query you would use List.Dates(), and in DAX you would use CALENDAR().

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...