Hi @will.marriott, You may recall we discussed a similar premise after we had a call, where we would need variables to control the custom modes date ranges. I proposed that variables to be used as offsets (in this case the parent date as @StuartH discusses ) would be the driver to show the previous month/day. I may be barking up the wrong tree here but sounds very very similar to the requirement we discussed. So sorry in advanced if I am confused.
Example
Day 1 of October, we have 100 people looking at month end close (30 Sept), I have had to double up on the number of custom widgets, so we have a widget showing prior month versus another showing current month. This could be handled in one custom mode widget if a variable was introduced as a “Parent” which governs the widgets custom date ranges.
Custom Mode: Rather than using a set date range, we could have the option of a variable which would be the point in time we wish to view. In @StuartH 's example the parent could be 1st October, but the custom column would be offset -1 to show yesterday. Implementing this in a user friendly fashion will be problematic, but relative referencing is what B.I. is all about. Examples in other systems are:
- Peek (use offset)
- Lag (use offset)
- Lead (use Offset)
- Or more exotic is MDX:
[Date].[Calendar].CURRENTMEMBER.PREVMEMBER.NAME
It would be great if the custom mode period options had something like this:
Date Variables created based on the databases defined Period Types:
“MyVariable” is used (discussed shortly) and bound to the “Month” Period Type, it will only allow the choices of month to be entered.
“MyOtherVariable” is used (discussed shortly) and bound to the “Day” Period Type, it will only allow the choices of Day to be entered.
So how do we create the variable?
I would suggest that this could be done one level up as part of the calculations definition:
This then adds an extra line, but instead of defining the measure, you define the date:
Note: You should be able to either type in the date (fixed) or potentially have a volatile expression such as GetDate() with offset up there as well!
By having this at the top of the Custom Mode, it allows flexibility with having a “Parent” date (based on the DB’s Underlying period types see below).
My biggest issue with custom mode is at year end/month end numerous widgets need to be updated. Think of what “Current Quarter” means, its relative to the month you’re in, using January as the starting point, Current quarter for October would be Q4, but looking at month end (Sept) it would be Q3, since custom modes offsets are ALWAYS relative to the current date, we are constantly adjusting widgets or adding additional period types and slowing down the database build time. Having a master value to base the dates off like this should help.
And as for my requirement it is one step further (but happy to wait!) which is allowing the user to enter the variable in the dashboard, this would revolutionise the dashboards into being truly dynamic with what if scenarios etc as one variable can alter the entire result set.
I appreciate there is quite a lot of things to iron out, but thought I should put my thoughts down on this post as I think this will reduce the time to alter month/year end dashboards and widgets considerably.
Here is what I meant by Period Types, in the designer of the DB, these are what the variables would be allowed to attach to:
Happy to discuss further if you believe this is a potential option.