Expression to select prior period data given month, year

[nickedw0]

I have a calendar with fields for Month, Year, MonthYear. Formatted as follows:

Month  Year  MonthYear

Jul          2017     Jul-2017

Jun         2017     Jun-2017

If selection for Month is Jul, Year is 2017, How do I count records for Jun-2017?

I have the following expression, but it returns nothing:

Count({$<MonthYear={"$(=Date(AddMonths(GetFieldSelections([Month],1) &'-' & GetFieldSelections([Year],1),-1),'MM-YYYY')"}>} InvoiceNo)

Any help appreciated, Thanks

[sunny_talwar]

How do you create MonthYear field in the script? Like this

Date(MonthStart(Date), 'MMM-YYYY') as MonthYear?

If you do, then you can try this

Count({$<MonthYear={"$(=Date(MonthStart(Max([Month], -1),'MM-YYYY'))"}, Year, Month>} InvoiceNo)

[vinieme12 · Accepted solution]

Also read

Dates in Set Analysis

[YoussefBelloum]

Hi,

Count({$<MonthYear={"$(=Date(AddMonths(GetFieldSelections([Month],1) &'-' & GetFieldSelections([Year],1),-1),'MM-YYYY')"}>} InvoiceNo)

it is returning nothing because here (for the first part) you compare a calculated field with a date, the format is different.. Also the input for the Addmonths function is also wrong

[nickedw0]

The following worked for me. Thanks everyone for your suggestions and guidance. I used guidance from ' Dates in Set Analysis". Since I was pulled data other than associated data, the $ had to be a 1.

Count( {1<MonthYear={"$(=Date(AddMonths(Max(MonthYear),-1),'YYYY-MMM'))"}>} LoanID )