Value between Two Dates

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 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.

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
)

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).

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.