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

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