Forum Discussion

Creative_tree88's avatar
Creative_tree88
Helper V
2 years ago
Solved

Finding a value between two dates

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 

Need Help
  • BIswajit_Das's avatar
    BIswajit_Das
    2 years ago

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

5 Replies

  • BIswajit_Das's avatar
    BIswajit_Das
    Impactful Individual
    2 years ago

    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's avatar
      Creative_tree88
      Helper V
      1 year ago

      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's avatar
        BIswajit_Das
        Impactful Individual
        1 year ago

        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's avatar
      Creative_tree88
      Helper V
      1 year ago

      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!