Lookup between two dates

[Edds01388]

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.

[Edds01388]

Thanks again - refreshed data and dates are now correct - just trying now to get this in number order.. not sure if this is possible

[v-junyant-msft]

Hi @Edds01388 ,

Please try this way:

Then the final output is as following:

Best Regards,
Dino Tao

[Edds01388]

Sorted it - my dates are wrong 🙂😂

[Edds01388]

Re-checked the data - for some reason its not checkinig it correctly - so a date in 2023 is appearing in a wrong period. 

[Edds01388]

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)

[Edds01388]

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

[Edds01388]

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.

[v-junyant-msft · Accepted solution]

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.

[Edds01388]

Hi I have just found an issue.

For some reason its missing a single date out of each period. 

The first image is a list of dates which the formula does not give a value - the second image is the list of dates (from and to) which is used to pull back the period, as you can see the first day after the start date is blank - help apprecaited

For example the Period from 17/09/2023 to 19/08/2023 - any date in that section should show 7-2023/24 - this works for every day BUT 18/09, this for each group 

[v-junyant-msft]

Hi @Edds01388 

The column "Title Matched" is just the "Ptitle" you want, sorry I forgot to change the name.

Best Regards,
Dino Tao

[Edds01388]

You are a Genius!! Fantasic and many many thanks for this 🙂 I am good at excel, but this is something new for me

[v-junyant-msft]

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.