I'm trying to create a modification to something like the CLOSINGBALANCEYEAR function.
I need to see the total value of a budget between 2 periods. Seems fairly easy but the issue i'm facing is i need to show the grandtotal between the periods at all times.
the point is to have a grand total modified by user context for the selected months without having to create a MTD, QTD and YTD value.
This example shows between months 1 to 3; but i'd need the solution to show me the grand total between dates 2 to 7 (example only) if the user selected it.
The schema is a fairly straight forward fact table showing fact.[DATE] and fact.[product], with both a dim.[date] and dim.[product] tables separate.
Any help is really appreciated, i'm having a lot of troubles getting the needed visual!
In the two screenshot, "Budget for selected period" is the grand total between dates 2 to 7 (example only) if the user selected it, It is your expected result, right?
In the "Budget" column, values only exsits in the first day of each month, it means for each whole month instead of one day, right?
For example, for month1, it has a budget 100, for month2, it has a budget 150.