Power Bi : non-unique value in dateadd()-CodePudding

I'm trying to create a metric to give me the number of request between two relative dates.

I tested this:

Request M = 
VAR StartDate = DATEADD('Request'[request_date],-20,DAY)
VAR EndDate = DATEADD('Request'[request_date],0,DAY)
RETURN
calculate(sum('Request'[request_count]),DATESBETWEEN('Request'[request_date], StartDate, EndDate))

And I have the error : "A table of multiple values was supplied where a single value was expected "

My column request_date looks like this :

5 july 2022
5 july 2022 
5 july 2022
5 july 2022 
6 july 2022
6 july 2022
7 july 2022
...

--> All my dates are present more than once.

So I tried to add a "firstdate", like this:

VAR StartDate = DATEADD(FirstDate('Request'[request_date]),-20,DAY)
VAR EndDate = DATEADD(FirstDate('Request'[request_date]),0,DAY)

This time I have no error, but I don't get the right value. Could you help me please ?

CodePudding user response：

Add a (unique) Date table to your model and setup a one-to-many relation between 'Date'[date] and your 'Request'[request_date]

Then use

Request M = 
VAR StartDate = 
    DATEADD('Date'[date], -20, DAY)
VAR EndDate = 
    DATEADD('Date'[date], 0, DAY)
RETURN
    CALCULATE(
        SUM('Request'[request_count]),
        DATESBETWEEN('Date'[date], StartDate, EndDate)
    )