Forum Discussion
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...
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 |
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:
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!