Forum Discussion

Creative_tree88's avatar
Creative_tree88
Helper V
1 year ago

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...
Need Help
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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's avatar
Creative_tree88
Helper V
1 year ago

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!