Forum Discussion

Edds01388's avatar
Edds01388
Regular Visitor
2 years ago
Solved

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)   w...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Edds01388
Regular Visitor
2 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Edds01388 

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

    Best Regards,
    Dino Tao

    • Edds01388's avatar
      Edds01388
      Regular Visitor
      2 years ago

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

  • Edds01388's avatar
    Edds01388
    Regular Visitor
    2 years ago

    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