Need Help With Date Formula

[pnn44794]

Hello:

I have a straight table where I am trying to show items that have been Approved Offline in the last seven days, with those seven days being Tuesday to Tuesday (i.e. 1/3/2017 to 1/10/2017).  I'm trying the following formula but nothing is returned though I know I have items that should be.

Status='Approved - Offline' and [Approval Date] <= date(max([Approval Date])-7)

Approval Date is in the format of mm-dd-yyyy.  I suspect I'm missing something fairly simple with this.

As always, any and all help is appreciated.  Thanks in advance.

[sunny_talwar]

What object are you looking to do this in? Text box object? straight table? If it is straight table, then what are you dimensions? Do you have an existing expression?

[satheshreddy]

Hi Perry,

can you post dimensions for understanding, better to try below Exp.

if([Approval Date] <= date(max([Approval Date])-7),(Status='Approved - Offline'),'No Approvals')

Regards

Sathish

[nagarjuna_kotha]

Hi Try this :

=Count({<Status={'Approved - Offline'},[Approval Date]={">=$(=Date(Max([Approval Date])-7,'DD/MM/YYYY'))"}>}ID)

Also if u have any issue check attched file.

-Nagarjun

[pnn44794]

Hello Sunny:

Sorry for the delay in replying.  This is for a straight table.  The Dimensions are Status and Approval Date.  The existing expression, which does not return anything, is as follows:

Status='Approved - Offline' and [Approval Date] <= date(max([Approval Date])-7)

What I'm looking for is when I do a reload of the data, I want the straight table populated with any items that have Status of "Approved - Offline" (without the quotes) and which have had their status set to "Approved - Offline" within the last seven days.  Otherwise, don't populate the straight table with anything.

[pnn44794]

Actually, I should probably also add that there are other dimensions that are a part of the table and which would then be displayed, but the two dimensions I gave are the two key dimensions for the expression.

[pnn44794]

Hello Sathish:

Sorry for the delay in replying.  This is the same response as I gave Sunny.

This is for a straight table.  The Dimensions are Status and Approval Date.  The existing expression, which does not return anything, is as follows:

Status='Approved - Offline' and [Approval Date] <= date(max([Approval Date])-7)

What I'm looking for is when I do a reload of the data, I want the straight table populated with any items that have Status of "Approved - Offline" (without the quotes) and which have had their status set to "Approved - Offline" within the last seven days.  Otherwise, don't populate the straight table with anything.  There are other dimensions that is Status = Approved - Offline and within the last 7 days based on Approval Date, that would be displayed.  Status and Approval Date I believe are the key fields for the expression.

[sunny_talwar]

May be use this as your expression:

Avg({<Status = {'Approved - Offline'}, [Approval Date] = {"$(='<=' & Date(Max({1}[Approval Date])-7, 'MM/DD/YYYY'))}>} 1)

or if you want to see this from today's date:

Avg({<Status = {'Approved - Offline'}, [Approval Date] = {"$(='<=' & Date(Today()-7, 'MM/DD/YYYY'))}>} 1)

[pnn44794]

Hello Sunny:

Thanks for the reply.  Both expressions have a mismatched (.  The exact error given is Token Doesn't Match.

Avg({<Status = {'Approved - Offline'}, [Approval Date] = {"$(='<=' & Date(Max({1}[Approval Date])-7, 'MM/DD/YYYY'))}>} 1)

[sunny_talwar]

Token doesn't match? That's the error you got?