Distinct Count between dates

[Dmichel0729]

Hello all, 

I am trying to change an expression that previously looked at a date range up to three months prior. I now need to change it to show data prior to 90 days. Below is the expression I have developed, but the output I receive in my chart is "-". The font in red is what I have added to change the max date to 90 days. Previously, the red text was Date(MonthEnd(Max(MonthYear), -3). 

Num(count(DISTINCT{1< [Site]={'Site Reviewed On Time', 'Site Reviewed Late'}, MonthYear={"$(='>=' & Date(MonthStart(Max(MonthYear), -13), 'MMM-YYYY') & '<=' & Date(=Date(today(), -90), 'MMM-YYYY')"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA)/count(DISTINCT{1< [Site]-={''}, MonthYear={"$(='>=' & Date(MonthStart(Max(MonthYear), -13), 'MMM-YYYY') & '<=' & Date(=Date(today(), -90)), 'DD-MM-YYYY')"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA),'#,##0%')

Any help would be appreciated! 

Thank you

Dylan

[Anil_Babu_Samineni · Accepted solution]

Perhaps this?

Num(count(DISTINCT{1< [Site]={'Site Reviewed On Time', 'Site Reviewed Late'},MonthYear={">=$(=Date(MonthStart(AddMonths(Max(MonthYear), -13)), 'MMM-YYYY'))<=$(=Date(MonthStart(AddMonths(Max(MonthYear), -3)), 'MMM-YYYY'))"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA)/count(DISTINCT{1< [Site]-={''}, MonthYear={">=$(=Date(MonthStart(AddMonths(Max(MonthYear), -13)), 'MMM-YYYY'))<=$(=Date(MonthStart(AddMonths(Max(MonthYear), -3)), 'MMM-YYYY'))"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA),'#,##0%')

[Dmichel0729]

Hello Anil, 

Where you stated '"<=$(=Date(MonthStart(AddYears(Max(MonthYear), -3))"  I am actually trying to make it reflect 90 days and no longer 3 months. Sorry my explanation in the question may have been a little misleading.  Do you know how I would change your above expression to reflect 90 days prior?

[Anil_Babu_Samineni]

What is your date field and how it is? Today() is not the correct one, IF you calculate MonthYear from DateField - You can try? But actually you should be having similar functionality when you compare MonthYear with Date so, I would suggest to use DateField instead MonhtYear. If not, Please explain more with some dataset ..

<=$(Date(Max(DateField, -30)))