Reporting on balance table with 12 columns as periods

[antexity]

Hope I can explain this right:

From my data source,  I have an account balance table (Sales) that contains a Fiscal Year column (FY2019, FY2020 etc) and 12 Period columns ( P01, P02 etc) and I have a warranty table which I aggregated warranty $ spent per YYYYMM.

I am trying to build a report that compares Warranty $'s per month over the sales account balance table to see the percentage of warranty $'s over sales $.  The tricky part is on my account balance table.  Not sure how to tell Cognos, if we are in March 2020,   then give me the last 12 months (Mar 2019 to Feb 2020) and then divide my Warranty $ / Sales $.  How can I list the columns values as FY2020 P02, FY2020 P01,  FY2019P12, FY2019P11 etc.

Right now if I query the table  I can only say FY 2019  and it shows $ amounts for p01 to p12,  if I go FY 2020, P01, 02 and 03 have values and 04-12 are $0.

Any help is appreciated!!!

[BigChris]

Would something like this work in your filter?

Code Select Expand

([Year] = year(current_date) and [Period] <= month(current_date))
or
([Year] = year(current_date)-1 and [Period] > month(current_date))