Date Range using formatting

[salleninsd]

Hello,

I am struggling with trying to write the correct formula for a date range in the current month.  If I write

Sum({$<[Episode.NonAdmitDate]={">=2018-10-01 <=2018-10-31"}>}[Episode.IsNonAdmit])

it works fine, but I want to automate the process so I don't have to adjust the timeline every month.  I'm sure I jacked up the syntax in this, and wondered if someone could give me a little nudge in the right direction.  This is what I am trying to do (and the date format in the field is YYYY-MM-DD):

Sum({$<[Episode.NonAdmitDate]={'$(>=floor(Date(Monthstart(Today(),'YYYY-MM-DD'))) <=floor(Monthend(Date(Today(),'YYYY-MM-DD'))))'}>}[Episode.IsNonAdmit])

I've tried using ' and " in the set, and I've tried to position the 'YYYY-MM-DD' in different places in the ()'s, but I just cant get it to work.

Any suggestions?  Thanks in advance for your help!

[salleninsd]

Oops, and I fixed the Monthstart(Date order, and it still didn't work (tried it both ways)

[Jesh19]

Try the below expression.

Sum({<[Episode.NonAdmitDate]={">=$(=Floor(Date(MonthStart(Today(),'YYYY-MM-DD'))))<=$(=Floor(Date(MonthEnd(Today(),'YYYY-MM-DD'))))"}>}[Episode.IsNonAdmit])

[salleninsd · Accepted solution]

Hi Jeshwanth,

Your syntax works!  I changed the Date(MonthStart to MonthStart(Date and the same for MonthEnd and it works fine:

Sum({<[Episode.NonAdmitDate]={">=$(=Floor(Monthstart(Date(Today(),'YYYY-MM-DD'))))<=$(=Floor(Monthend(Date(Today(),'YYYY-MM-DD'))))"}>}[Episode.IsNonAdmit])

I really appreciate your help.  This one will go into my notebook

Thanks again, and have a terrific weekend!