Need your help and suggestions for the below scenario:
I have a report with the fields: Customer, Average Usage in last 3 months and Usage in Current month.
Filter is on Month/Year. Month/Year field is a string at DB level.
Can you please suggest how to calculate the average usage for the last 3 months by taking the user input for the current Month/Year.
Since the report is collecting only one month data. Now you can't get the previous three months information. Please let me know the DB information. If you are using the BEX as backend, you can create the prompt variable in Bex and you cal calculate the last three month average.
In this situation, you have to collect the data for entire fiscal year. Here you should have to use "Fiscal Year" and "Month/Year" prompts in "OR" operator. Now the report will collect one complete fiscal year data. Here the disadvantage is performance and need to apply current month filter for required measures.
Then you can filter the measure to current "Month/Year". Also you can calculate the previous three months average. I hope, this will resolve your issue.