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

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'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