Forum Discussion

Creative_tree88's avatar
Creative_tree88
Helper V
1 year ago

Value between Two Dates

Hi - I have the following DAX which is in a calculated column.  The issue I'm having is that it's saying there are multiple values where single is expected.  Is there any way of adapting this express...
Need Help
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

Hi Creative_tree88 ,

As i mentioned in my reply, ised the Min function just for example, as these variables reference another table, you need to aggregate them as your need, because its expected one value not multiple values, if you help me showing the expected output i can refine the logic, can you please give me more details of your expected result?

 

 

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi Creative_tree88 ,

    Did you got the solution? if yes, please consider to accep the correct repply as solution.

     

     

     

    • Creative_tree88's avatar
      Creative_tree88
      Helper V
      1 year ago

      Bibiano_Geraldo Many thanks.  It works well.   Just wondered how the DAX could be adapted to pick up exams where there is no exam on the 'Sales Letter A Date', in which case assume a date of the end of the month and have this as the 'Sales Letter B Date' so find the exam between the two dates 'Sales Letter A Date' and 'Sales Letter B Date' (assumed end of month date if no match for single 'Sales Letter A Date).

      Does that make sense??

      • Bibiano_Geraldo's avatar
        Bibiano_Geraldo
        Super User
        1 year ago

        I'm happy it works, please consider to mark the repply as solution.

         

        Now for other question, i suggest you to try the following DAX:

        Value Between Two Dates Lookup = 
VAR _start = 'Finance Data'[Sales Letter A Date]
VAR _end =
    IF(
        ISBLANK('Finance Data'[Sales Letter B Date]),
        EOMONTH(_start, 0),
        'Finance Data'[Sales Letter B Date]
    )
VAR _key = 'Finance Data'[KEY]

VAR _codesInRange =
    FILTER(
        'Sales Data',
        'Sales Data'[Sales Date] >= _start &&
        'Sales Data'[Sales Date] <= _end &&
        'Sales Data'[KEY] = _key
    )

VAR _result =
    CONCATENATEX(
        _codesInRange,
        'Sales Data'[Code],
        ", "
    )

RETURN
IF(
    ISBLANK(_result),
    BLANK(),
    _result
)