MTD expression doesn't work

[naiduvc467]

Hello All,

My MTD expression works ok if I don't put Date field in the dimension, but I want to see the count of attendances (MTD )on each day.

count({<[Date]={">=$(=(Date(Monthstart(Max(Date)),'YYYY-MM-DD')))<=$(=(Date(Max(Date)-1,'YYYY-MM-DD')))"},_Month=,[_Fiscal Year]=,[IS4hrEDBreach]={'No'},Type={'AandE'}>}ID)

I have gone through so many posts in the community  for help tried my best before I post it here, please let me know what I am missing here.

Thanks

[sunny_talwar]

Try this

RangeSum(Above(Count({<[Date], _Month, [_Fiscal Year],[IS4hrEDBreach] = {'No'}, Type = {'AandE'}>} ID), 0, DayField))

or if you don't have a day field, you can try this...

RangeSum(Above(Count({<[Date], _Month, [_Fiscal Year],[IS4hrEDBreach] = {'No'}, Type = {'AandE'}>} ID), 0, Day(Date)))

[naiduvc467]

Hello Sunny,

Thanks for your quick reply, I might have written it wrong actually I want it for each date in a full month I did tried with range sum but if I use range sum and if I want to show rolling 7 days it's summing few days from previous month aswell.

I hope below image will be useful in knowing what I am trying to achieve. I want to show rolling 7 days of MTD.

[sunny_talwar · Accepted solution]

Are you using RowNo() at the end or DayField? You need to use DayField or Day(Date) so that the accumulation restarts at the end of the month.

[naiduvc467]

Thanks Sunny it works