topic Re: Using Measure in DATESBETWEEN Function in DAX Commands and Tips

Using Measure in DATESBETWEEN Function

DennisG — Fri, 07 Jul 2023 17:11:56 GMT

My question is can I use a measure in the DATESBETWEEN dax function?

Using TODAY() this works just fine I can slice by any date range. I would like to substitute a measure for TODAY(). The measure would be a selected date from a separate calendar table example

TargetDate = SELECTEDVALUE(CalendarDynamicTarget[Date]) I simply want to

I simply want to allow the user to pick a single date and have the period table use that date as the Target Date and create date ranges plus or minus that date.

I have a calculated table defined like this

Period =

VAR

_Next30Days = ADDCOLUMNS(

CALCULATETABLE(

'Calendar'

,DATESBETWEEN('Calendar'[Date],[TargetDate], [TargetDate]+30)

)

,"In the Next", "a_1 to 30 Days","ColorYN",0

)

VAR

_Next60Days = ADDCOLUMNS(

CALCULATETABLE(

'Calendar'

,DATESBETWEEN('Calendar'[Date],TODAY()+31, TODAY()+60)

)

, "In the Next", "b_31 to 60 Days","ColorYN",0

)

VAR

_Next90Days =

ADDCOLUMNS(

CALCULATETABLE(

'Calendar'

,DATESBETWEEN('Calendar'[Date],TODAY()+61, TODAY()+90)

)

, "In the Next", "c_61 to 90 Days","ColorYN",0

)

Re: Using Measure in DATESBETWEEN Function

WinterMist — Fri, 07 Jul 2023 21:33:13 GMT

DennisG

I'm not sure why, but DATESBETWEEN doesn't seem to work for me either.

However, you can use GENERATESERIES to return the period of desired dates based on the [Target Date] selected from the slicer.

NOTE: I used a range of the last 10 days (including today), but this is arbitrary. Use any range you like.

Perhaps someone else may know why DATESBETWEEN returns no records in this situation?

Hopefully GENERATESERIES works for you.

Regards,

Nathan

Re: Using Measure in DATESBETWEEN Function

DennisG — Fri, 07 Jul 2023 21:41:23 GMT

Thank You WinterMist for the reply my issue is I need a dynamic date picked by the user used instead of TODAY() that way they can generate a report from any given date with plus or minus date ranges at will. I hope I am making sense.

Re: Using Measure in DATESBETWEEN Function

WinterMist — Fri, 07 Jul 2023 22:01:15 GMT

DennisG

My apologies for the screenshots still referring to TODAY().

It's the same solution.

You just replace it with the dynamic measure and it works, as long as you're using GENERATESERIES.

NOTE: I only added the CountRowsInPeriod VAR so that it would return a scalar value for testing. If you're returning the table, then you'll need to use it in another way (e.g. as a filter argument or something).

Regards,

Nathan