Deriving the current Month
We are using the line item 'Month' in one of the module, and the objective is to derive the current month so that we can capture the purchase trend of our hardwares.When I try to use the formula as Month= ITEM(Time), I am getting the error message as shown in the screenshot. I have also attached the settings of the Model calendar.
Also, we want the Month to display the month dynamically, i.e. In Dec '20 , it should show Dec-20 and Jan-21 in Jan'21 .
Any idea how to to achieve this functionality?
Can you check the module (line item) that you intend to use the Time function with has got Model Calendar applied to it? [The screenshot you shared does not show that part to the right]. The Item formula returns items from the applies to dimensions. In your case it searches for Time ... most likely there is no time applied there.
That would be my step 1.
Next ... if you want the Current Period selected from time settings to drive the line item I would recommend using Currentperiodstart formula (link )
If you combine it with the start(item(time)) it will return a boolean in the month that is set as current one. [currentperiodstart() = start(item(time))) ]
From there you can do some magic and apply some extra conditions f.e. if you only want to look at 12month view ... or +/- 6months [lots of options there].
It is also recommended to have the time settings in a different module, so create one that will do what you want it to do.
If you are only interested in 1 cell saying that this is September 20 (Sep20), then you still can use the period(currentperiodstart()).
Have a play with it 🙂2
ITEM function is used to retrieve a list item which is contained within the module as a dimension in the applies to or a time dimension.
The error message indicates that your module is not dimensioned by time.
However, what you are trying to achieve does not require the module to be dimensioned by time.
As the current period is a setting that can be used across you model I would create a system module where you can hold this data centrally and then reference it from any point in the model. This is best practice and saves having to recreate the formula multiple times.
Use the formula as suggested by @Noward but contain it within a centrallised systems module containing no dimensions but simply line items.
Reference this directly in your target to pull the data across.
You can also use this centralised systems module to pull through current year, current version, next year, next month etc.
This will make you model more efficient.
Very small correction, you don't need to add item(time) as a parameter within your Start() function. Just do start() and it will default to the current period of that line item.
Thanks @ChrisAHeathcote @rob_marshall @Noward for the tips. Let me try out.