Value between Two Dates

[Creative_tree88]

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

)

Sample Data

[Bibiano_Geraldo]

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]

@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]

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.

[Creative_tree88]

@Bibiano_Geraldo Thanks, but when I add that, I don't get any data at all.  The errors are gone, yes, but no data is populated...?

[Bibiano_Geraldo]

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?

[Creative_tree88]

@Bibiano_Geraldo As an example - I've found 4 lines of data which show what I need.  The examinations column will be what this formula will eventually display.  It will find the examinations between two dates and populate - if just one date, it looks specifically at that date and pulls the examination.  Perhaps there is another DAX method I can use?  Any help much appreciated.

KEY	Sales Letter A Date	Sales Letter B Date	Examinations
447064	02/10/2023	08/10/2023	UDRAII
471947	15/10/2023		ZNCONS
539305	25/10/2023	25/10/2023	UINJTJ
651438	15/09/2023	10/10/2023	FNRBK

[Bibiano_Geraldo]

Hi @Creative_tree88 , 

In your Finance Data, Add a new calculated column with the following DAX code:

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 _codesInRange =
    FILTER(
        'Sales Data',
        'Sales Data'[Sales Date] >= _start &&
        'Sales Data'[Sales Date] <= IF(ISBLANK(_end), _start, _end) &&
        'Sales Data'[KEY] = _key
    )

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

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

Your output should look like this:

[Creative_tree88]

@Bibiano_Geraldo So far that looks pretty good!  Is it OK to come back to you if I find some anomolies, just to see if anything can be done to resolve these too?  Many thanks indeed!

[Bibiano_Geraldo]

No problem, @Creative_tree88 ,

[Creative_tree88]

@Bibiano_Geraldo Sure - give me a few minutes.

[Bibiano_Geraldo]

Hi @Creative_tree88 ,

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

[Creative_tree88]

@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]

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
)

[Creative_tree88]

@Bibiano_Geraldo Many thanks.  That looks great - however, instead of month end, in the DAX you've offered, is there a way of just using the month of 'Sales Letter B Date' - in the sample data I supplied, it's always October 2023.  But this will change each time I use a new data source - I just need to the DAX to pick up the month of the Sales Letter B Date and apply to the DAX you've supplied above.  Huge thanks!

[Bibiano_Geraldo]

Hi @Creative_tree88 ,

Have one of this reply solved your problem? please consider to accept as solution.

About separeted rows, i gave your answer in another post, but just for context, i'll paste here the reply:

You can achieve the desired result by creating a new calculated table using the following DAX:

NewTable = 
VAR Separator = "|"
RETURN
SELECTCOLUMNS(
    GENERATE(
        'YourOriginalTable',
        VAR ExaminationsList = SUBSTITUTE('YourOriginalTable'[Examinations], ",", Separator)
        RETURN 
        SELECTCOLUMNS(
            GENERATESERIES(1, LEN(ExaminationsList) - LEN(SUBSTITUTE(ExaminationsList, Separator, "")) + 1),
            "Report ID2", [Report ID],
            "SplitValue", PATHITEM(ExaminationsList, [Value], TEXT)
        )
    ),
    "Report ID2", [Report ID2],
    "SplitValue", [SplitValue]
)

Your output will look like this:

Make sure to replace table and columns names with your owns.

[Bibiano_Geraldo]

Hi @Creative_tree88 ,
To adapt the DAX formula to dynamically use the month of Sales Letter B Date (or default to the same month as Sales Letter A Date if Sales Letter B Date is blank), you can modify the calculation of _end to ensure it always picks up the correct month.

Updade DAX:

Value Between Two Dates Lookup = 
VAR _start = 'Finance Data'[Sales Letter A Date]
VAR _end =
    IF(
        ISBLANK('Finance Data'[Sales Letter B Date]),
        DATE(YEAR(_start), MONTH(_start), DAY(EOMONTH(_start, 0))), 
        DATE(YEAR('Finance Data'[Sales Letter B Date]), MONTH('Finance Data'[Sales Letter B Date]), DAY(EOMONTH('Finance Data'[Sales Letter B Date], 0)))
    )
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
)

if Sales Letter B Date is blank the end date is set to the last day of the month of Sales Letter A Date using EOMONTH(_start, 0).

If Sales Letter B Date is not blank it uses the last day of the month of Sales Letter B Date using EOMONTH('Finance Data'[Sales Letter B Date], 0).

[Creative_tree88]

@Bibiano_Geraldo Huge thanks for this.  Really working nicely.  Just quick quesiton, the output can often result in a number of examinations, separated by comma, on one line.  Is there any way of separating each of these, on to a separate row?  So, if there is a multiple set of results, separated by comma, it gets put onto a separate line?  Thanks so much.

[danextian]

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 )