Finding a value between two dates

[Creative_tree88]

Hi all - I need to find a value, most likely using lookup function??  Not sure.  But the value is between a range, consisting of two dates.  However, the key I'm using to lookup, has multiple values attached to it so I can't just use the 'KEY' on it's own in this example, I need to specifically find the corresponding value in between the date range (Admission and Discharge).

I've attached a link to the sample data (really cut down but is representative of overall cohort).  Occasionally there is no discharge date, so will just need to use admission date only.  For your reference, the correct lookup is identified in my sample date as yellow filled, for ease to identify.

Any ideas?  I can't just use a lookup, as there are multiple values, but also HAS to be within the range given.

Thanks to @BIswajit_Das you helped me in the first request like this, but I didn't take into account the multiple values in my sample data, and hence the need to locate the correct value in a specific date range, between the admission date and discharge date.

Appreciate your help - this one has really perplexed me!!!!  

Sample Data 

[BIswajit_Das · Accepted solution]

Hello @Creative_tree88

Try this DAX out
(Here 'Table1' is 'Data' & 'Table2' is 'Lookup Data')

#EXAM2 =

VAR _start = 'Table1'[Admission]

VAR _end = 'Table1'[Discharge]

VAR _key = 'Table1'[KEY]

VAR _singleDateExam =

    LOOKUPVALUE(

        'Table2'[Exam],

        'Table2'[Event Date].[Date], _start,

        'Table2'[KEY], _key

    )

VAR _multiDateExams =

    CALCULATE(

        CONCATENATEX(

            FILTER(

                'Table2',

                'Table2'[Event Date].[Date] >= _start &&

                'Table2'[Event Date].[Date] <= _end &&

                'Table2'[KEY] = _key

            ),

            'Table2'[Exam],

            ", "

        )

    )

RETURN

IF(

    ISBLANK(_end),

    _singleDateExam,

    _multiDateExams

)

If you found this information useful, please consider marking it as the accepted solution.

Thanks & Regrads...

[Creative_tree88]

@BIswajit_Das I'm now getting a message on this:

Column reference to 'Event Date' in table 'Activity' cannot be used with a variation 'Date' because it does not have any.

Any idea why this would be happening?  Obviously now returning error in the column.  Many thanks!

[Creative_tree88]

@BIswajit_Das - I've just tried to use this same method on an updated table, based exactly on the previous one, but it's now saying 'ERROR - A table of multiple values was supplied where a single value was expected'

I really do not understand as I have not changed anything at all...any advice at all?   Thank you.

[BIswajit_Das]

Hello @Creative_tree88 
I think you're creating a measure/table in which you are returning a column of multiple values.

"ERROR - A table of multiple values was supplied where a single value was expected" this error
occurs in DAX when a function expects a single value but instead receives a table or multiple values.

Create a calculated column and It Suppose to work
If it does not work then attach the demo data i'll try to fix the DAX

Thanks & Regards...

[Creative_tree88]

@BIswajit_Das I added a new column and copied the DAX into the formula bar at the top.  Did it the same way as previously.  For some reason it's not working unlike the previous month when it worked perfectly.  I'm trying to understand if I have done something differently to last time...but so far am a bit confused as to why all of a sudden it's stopped working...

I'll send you some demo data.