Using a measure as a date column input in DATESINPERIOD

[acamar22]

Hi all

I am trying to create some dynamic DAX code:

VAR DateRange = CALENDAR( MIN('FailureData[Date]),  MAX('FailureData[Date]) )

RETURN

CALCULATETABLE( other code ,  DATESINBETWEEN( DateRange, StartDate, -1, MONTH)   )

I am getting this error: DatesINPeriod functions are only accepting date column reference as a first argument.

I tried to use SELECTCOLUMN() for DateRange, but I get the same result.

I believe I just need to convert this single-column table into a column, but I'm not sure how. Any ideas?

Thanks

[Anonymous]

Hi @acamar22 ,

Thank you @VN999  very much for the solution, and I've tried other ways to help you understand the problem:

When you make a mistake with SELECTCOLUMN(), my advice is to use VALUES for this

VAR DateRange = CALENDAR(MIN('FailureData'[Date]), MAX('FailureData'[Date]))
RETURN
CALCULATETABLE(
   'OtherTable',  // Replace with your actual table name or expression
   DATESINBETWEEN(
       VALUES(DateRange[Date]),  // This is a date column reference
       StartDate,  // 
       EDATE(StartDate, -1)  // 
   )
)

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[acamar22 · Accepted solution]

Hi, thanks for the suggestion, but I am still getting the same error, I've solved this by using FILTER() instead

[VN999]

Try with this below DAX code might help you:

VAR DateRange =
CALENDAR(
MIN('FailureData'[Date]),
MAX('FailureData'[Date])
)

RETURN
CALCULATETABLE(
'OtherTable', -- Replace 'OtherTable' with your actual table name or expression
DATESINPERIOD(
DateRange,
MAX('FailureData'[Date]), -- End date of the period
-1, -- Number of intervals to go back (-1 means previous month)
MONTH -- Interval type (MONTH, QUARTER, YEAR)
)
)

[acamar22]

Hi @VN999 

Thanks for the response, however, this is what I already had.

The main problem is that the VAR DateRange is not being accepted as the first input into DATESINPERIOD().

I am trying to figure out why and how to fix it.