Return a value based on date start and date end

Question

Need help please!&nbsp;&nbsp;I need to return a value based on a date range. I have two tables, one has the date (which i need to return the id) and the other has the date start and date end of the coresponding ids. I have made two smaller tables as an example.&nbsp;My first table provides the date start and date end of each id. I need to populate the id column in the table below.&nbsp;&nbsp;&nbsp;&nbsp;These tables are just mockups, I have a much larger tables (with more ids and dates) loaded into power bi already. I could do it in excel using a VLOOKUP but can not work out how to make it work in Power BI.&nbsp;&nbsp;Any help would be much appreciated!&nbsp;

johanno · Accepted Answer

If I name the first table to Intervals and the second to Dates you could try:Id = 
CALCULATE(
       VALUES(Intervals[Id]);
       FILTER(
           Intervals;
           Intervals[Date_Start] &lt;= EARLIER(Dates[Date])
           &amp;&amp; Intervals[Date_End] &gt;= EARLIER(Dates[Date])
       )
)

Forum Discussion

Return a value based on date start and date end

2 Replies

Related content

Measure returning incorrect value

Date Different return wrong value

Return text value based on filter

MAXX Returning duplicate values

Return a value based on a condition