Forum Discussion
Value between Two Dates
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.
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...?
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 Sure - give me a few minutes.
Hi Creative_tree88 ,
Did you got the solution? if yes, please consider to accep the correct repply as solution.
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: