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 expression, or even creating a new one to get over this issue?

I'm trying to lookup the respective examination in Sales Data table, based on the Finance Data 'Sales Letter A Date' and 'Sales Letter B Date'.  There's usually a value which will come up between these dates.  Where there is just one date i.e. 'Sales Letter A Date' it'll just match the respective sales date on the Sales Data and should pull this back only.  Hence the need for two expressions here - one for multi dates, the other for single dates.

Any help much appreciated.  I had help from BIswajit_Das previously on this.  If you're there BIswajit_Das I could sure do with some help.  If anyone else has good ideas, please share.

I've attached sample data relating to the tables.

Value Between Two Dates Lookup =
VAR _start = 'Finance Data'[Sales Letter A Date]
VAR _end = 'Finance Data'[Sales Letter B Date]
VAR _key = 'Finance Data'[KEY]

 

VAR _singleDateCode =
    LOOKUPVALUE(
        'Sales Data'[Code],
        'Sales Data'[Sales Date], _start,
        'Sales Data'[KEY], _key
    )

 

VAR _multiDateCodes =
    CALCULATE(
        CONCATENATEX(
            FILTER(
                'Sales Data',
                'Sales Data'[Sales Date] >= _start &&
                'Sales Data'[Sales Date] <= _end &&
                'Sales Data'[KEY] = _key
            ),
            'Sales Data'[Code],
            ", "
        )
    )

 

RETURN
IF(
    ISBLANK(_end),
    _singleDateCode,
    _multiDateCodes
)
 
 
Need Help

18 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Creative_tree88 

    The issue lies with using LOOKUPVALUE as it expects a single value to be returned if  the optional parameter alternateResult is not specified. This error doesn't occur if using your sample data though.

     

    I'm not sure what should be returned when there are multiple values, as the expectation is that there should only be one result if end is blank. You might need to review your logic. However, if there are indeed two or more results, you could simply return _multiDateCodes as the alternate result.

    Value Between Two Dates Lookup =
VAR _start = 'Finance Data'[Sales Letter A Date]
VAR _end = 'Finance Data'[Sales Letter B Date]
VAR _key = 'Finance Data'[KEY]
VAR _singleDateCode =
    LOOKUPVALUE (
        'Sales Data'[Code],
        'Sales Data'[Sales Date], _start,
        'Sales Data'[KEY], _key
    )
VAR _multiDateCodes =
    CALCULATE (
        CONCATENATEX (
            FILTER (
                'Sales Data',
                'Sales Data'[Sales Date] >= _start
                    && 'Sales Data'[Sales Date] <= _end
                    && 'Sales Data'[KEY] = _key
            ),
            'Sales Data'[Code],
            ", "
        )
    )

RETURN
    IFERROR( singleDateCode, _multiDateCodes )

     

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi Creative_tree88 , 

    As i see in the organization of your variables, this calculated column should be placed on Finance Data not in Sales Data.

     

    Try it and tell me if you got the desired output.

     

    Thank you

    • Creative_tree88's avatar
      Creative_tree88
      Helper V
      1 year ago

      Bibiano_Geraldo  Many thanks.  It is indeed in the finance data, not the sales data.  It has worked previously, so I'm scratching my head a little with this one!

      • Bibiano_Geraldo's avatar
        Bibiano_Geraldo
        Super User
        1 year ago

        If you want the calculated column in sales data, you have to work in this part of your DAX:

        VAR _start = 'Finance Data'[Sales Letter A Date]
VAR _end = 'Finance Data'[Sales Letter B Date]
VAR _key = 'Finance Data'[KEY]

         

        This is bringing the entire column, not a single value, thats way you got the error: A single value for column 'Sales Letter A Date' in table 'Finance Data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count...

         

        if you use aggregations suc MAX, MIN in that part of your code as shown bellow, you will not get the error:

        VAR _start = MIN('Finance Data'[Sales Letter A Date])
VAR _end = MIN('Finance Data'[Sales Letter B Date])
VAR _key = MIN('Finance Data'[KEY])

         

        I used MIN function, just for example, reform as you need.