Hi Djshweiz there may be a Sage Intelligence expert here to help but if you're in a hurry, Alchemex, the Sage Intelligence developers, provide excellent report-writing support.  It might be worth getting in touch with them.

Thanks for your response Accsys Consulting AU . My query is in regards to SEI done by Nectari not the Alchemex

Oops I missed the "Enterprise" in the title.  Apologies.

HI Djshweiz ,  I just tried this module in my SEI environment and you can get those results by setting your table up as follows:  No need for a calculated column. 

Groups: Fiscal Period, Transaction Date

Pivots: Fiscal Year

Columns: Document Number, Sales Net

This will show all fiscal years in the columns, then filter on the last two years that you want to view.  This will give you two years comparative date sorted by fiscal month. 

If you only want it to show the current fiscal month this year vs last year, you can remove the fiscal period from the grouping and then add in a Global Variable to use CurrentFiscalPeriod as a filter and it will group by Transaction date and show the daily sales with last year comparison in table.  

Hope that helps. 

just a note on the Global Variable for Fiscal Period and Fiscal Month,  I can't remember if we built that one or it was a stock SEI global variable, so you may not have that one in your list.  I have it as @@CURFSMONTH in my system.  Just wanted to note that in case you go looking for it and its not there. 

No Problem. Thank you for your response

Hi Kyle, Thank you for providing different option. As per your suggestion  I have done the following:

Groups: Fiscal Period, Transaction Date

Pivots: Fiscal Year

In the advanced filter, I have added a formular  (Fiscal year ='@@Curyear' and fiscal period =@@CURMONTH) Or( Fiscal year ='@@Curyear' -1 and fiscal period =@@CURMONTH).

The solution seems to work but the only issue is on presentation, the system is not placing the transaction of same date  in one row or simply  hide last year breakdown and just show totals.

Please note that I was to add another  grouping  and the end result is supposed to look like the screenshot below.

 Djshweiz ,  I didn't think that one all the way through...you will need a calculated field to remove the year from the date field.   The problem you are running into is that Sage does not store the date without a year, so there is no date field for just "Month-day".  You'll need to create that in the Data Model and then you can group by Month/Day.   You can try and create a calculated field in the Data Model that will trim the first 4 characters from your date field in that date model.  That would give you only Month and Day date that you can use in your grouping instead of the Transaction Date, which should allow you to group by fiscal period.