Date formula

Question

Hi,

Note- kperiod is financial period so 20201 is April.

In the below example i have rev for each period, however i only want the to take forward the latest month end. so from 202101 to 202110 then when feb ends it would add feb rev to it.

I know it will be true/ false criteria but struggling to place in a computation in for that.

What would be the approach please

Many thanks

chiragpatel_1 · Answer

Hi Matt,

Thanks for the below.

Few questions for my undertsanding.

Can you explain the logic of the formula, so what it would produce. Would it take forward the periods 202101 to 202110 and then when feb ends add feb to that list and so on?

Matt_D · Answer

Hi @chiragpatel_1 if I'm getting this right, I would parse your date but add 3 months then use a filter tool to dynamically only take forward once the calendar month had passed?

New Date - DateTimeAdd(DateTimeParse(ToString([kperiod]), "%Y%m"), 3, 'months')

Custom filter - [New Date] < (DateTimeFormat(DateTimeToday(), "%Y-%m") + "-01")

Alternatively you could stick it all in a custom filter - DateTimeAdd(DateTimeParse(ToString([kperiod]), "%Y%m"), 3, 'months') < (DateTimeFormat(DateTimeToday(), "%Y-%m") + "-01")

Is this what you're after?

Matt