I need to create an OBIEE report wher I can show the MTD values for each month, but only till the present date for every month, i.e., if today is the 7th, I would like to see MTD of the present month from 1-7th of the present month vs MTD from 1-7th of previous month. Any idea?
Can you make RPD changes? If so I would do something like this: Create a repository variable that tracks what day number you are on (like 7 for the 7th day of the month), and different variables for the start date of the current and prior month.
After that, then in my column formula for current MTD, I would have a case statement saying when some date is between the start date of the month and start date of the month plus the number of days into the month (using the timestampadd function perhaps) then give me my measure amt/qty.
I would do the same thing for the prior MTD, except using the start date of the prior month, and adding the same number of days from there.
Of course you could do the same thing in the front end if you dont have access to the RPD, by doing some sort of timestampdiff between the current day and the start of the month, and using that in front end case statement calculations.