Time Periods Calculation

Solved4.11K viewsFormulas and Functions

I have created a TimePeriods matrix where I have entered additional (meta) items for future model use.

In a separate matrix, I want to enter a data representing the last period actuals have been entered.

In the TimePeriods matrix, I want my Date item to self-populate with a 1 or a 0. (1 = actual data is populated in the model, 0 = no actual data in the model).

As a new user, 1) I’m probably making this too hard, and 2) there are probably other ways to better address my requirement.

For now, I’d just like to nail down the structure of the model of the above. I’ve also included a spreadsheet which shows an example.

If I cannot add 2 attachments to a question, I’ll add my Quantrix model in this post. I’ll add the xls file in the second post.

Sorry if this is too easy of a question (warning: I’ll have more).



Hi mark,

QM allows a more compact solution. To solve your problem try this model.


Hey Mark,

I have had a go at your time periods problem. Let me know if this is what you were looking for and if you need some more clarification – I tried to lay it out so it was easy to understand how the formulae were working.





And here is the XLS file as mentioned in my message above.

I think the Excel file better conveys the ultimate objective.



SAU – very cool, thanks!

Not to take away what James did – he provided a multi-step algorithm allowing me to see the formulas. So his solution was instructive.

Unwittingly, you did what I was wanting to accomplish.

But again, thanks to both of you a ton.

Incidentally, the items Historical and Plan were only for illustrative purposes for an intern. Historic/actuals data will be stored in a separate matrix. Ditto for plan data.

Hope everyone can put up with me for a while – I model in other platforms, so I’m still getting used to the matrix paradigm.



Neat and elegant.

Thanks Sau


James, you nailed it!

Yes, the CurrentDate matrix (per your file) was just a place holder. I like what you have done.
But studying the formula is a huge help.

Next, and for the user experience, somewhere (probably on a canvas), I’ll have a filter where dates can be selected. Sorry, I’m going to figure that one out myself as I already have an example elsewhere here in the forum.