Forum Discussion
Value between Two Dates
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 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 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!