topic Re: Using a measure as a date column input in DATESINPERIOD in DAX Commands and Tips

Using a measure as a date column input in DATESINPERIOD

acamar22 — Sun, 30 Jun 2024 23:25:17 GMT

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

Re: Using a measure as a date column input in DATESINPERIOD

VN999 — Sun, 30 Jun 2024 23:39:58 GMT

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

Re: Using a measure as a date column input in DATESINPERIOD

acamar22 — Mon, 01 Jul 2024 02:21:24 GMT

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.

Re: Using a measure as a date column input in DATESINPERIOD

Anonymous — Tue, 02 Jul 2024 02:29:26 GMT

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.

Re: Using a measure as a date column input in DATESINPERIOD

acamar22 — Wed, 03 Jul 2024 16:22:29 GMT

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