Forum Discussion
Lookup between two dates
HI I have had a look at verious other similar problems but not been able to figure out how to replicate something I have.
In Excel - I used the following formula: =VLOOKUP(B353,A1:A55,3,TRUE) where B353 is a date, and Row A1 is a date, and Row 3 is a value.
The problem is I have two tables
Table one - "tracker" with lots of fields, I want to insert a new coloumn into "tracker", which looks up the field 'tracker' [Date], then looks at the table "periods" and if the date is after "Pdatefrom" but before "Pdateto" , then give the value in the cell "PTitle".
Example data
Column A = Pdatefrom
Column B - Pdateto
Column C = PTitle
In simple terms vlookup or array lookup in excel would bascially look at the date, then check to see if that date is between two other dates then return the value from that table.
Hi Edds01388 ,
Do you want this:
I will first show you the sample data:Then use DAX to create a new column:
Title Matched = CALCULATE( FIRSTNONBLANK('Period ends'[Title], 1), FILTER( ALL('Period ends'), 'Safety Tracker 2023'[Date] >= 'Period ends'[Pdatefrom] && 'Safety Tracker 2023'[Date] <= 'Period ends'[Pdateto] ) )The final result is shown below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 Replies
Hi Edds01388 ,
I apologize that I may not have understood which column you were hoping to compare. Is the 'tracker' [Date] column being compared to see if the date is between "Pdatefrom" and "Pdateto"? If so, please provide some additional data for the 'tracker' [Date] column.
Also I'm not sure what the "Period" column does in your screenshot?Based on the needs you describe, I will give you a reference DAX with which to create a new column:
PTitle = IF( 'Table'[Date] >= 'periods'[Pdatefrom] && 'Table'[Date] <= 'periods'[Pdateto], TRUE(), FALSE() )You can change this DAX to suit your own situation.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi thanks for the reponse
Table "tracker filed "date - this holds a date in the formate dd/mm/yy
Table "periods" - this has three fields - Date From, Date to and the Period. The new coloumn I am trying to create will look at the date, then compaire it with the dates in the "periods".
If a date in the "tracker" is within a date range in the "periods" (Pdatefrom" and "Pdateto") then is give the value for example
Tracker - Date 02/04/2009 - the new coloumn in the tracker will magically look at this date, go to the "period" table and check were this date is equal to or after to "Coloumn A" but before or equall too "Coloumn B", in this case the cell will return the value 1-2009/10
Date 24/07/2009 would give the value of 5-2009/10
This may help
I have tried adding a new coloum into the "safety tracker 2023" table called PTitle with the following
The above is the table which holds the dates and periods, I need to be able to look up the date from the "safety tracker" from the above values, then add in the value from the first coloum above if the date is between it.
Hi Edds01388 ,
Do you want this:
I will first show you the sample data:Then use DAX to create a new column:
Title Matched = CALCULATE( FIRSTNONBLANK('Period ends'[Title], 1), FILTER( ALL('Period ends'), 'Safety Tracker 2023'[Date] >= 'Period ends'[Pdatefrom] && 'Safety Tracker 2023'[Date] <= 'Period ends'[Pdateto] ) )The final result is shown below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Manged to get the chart created thank you again- couple of minor glitches which I am trying to figure out
1. X axes- getting this in a decent correct order 1,2,3,4 not how its showing
2. pulling a few wrong dates (but think I know why)
Re-checked the data - for some reason its not checkinig it correctly - so a date in 2023 is appearing in a wrong period.
Sorted it - my dates are wrong 🙂😂
Thanks again - refreshed data and dates are now correct - just trying now to get this in number order.. not sure if this is possible
