Hi! I've been working through a formula which is starting to give me fits. I am trying to allocate a charge across multiple months for financial reporting purposes. I am given a start date and an end date with a max date range of 60 days so it could possibly spread a portion of the charge across three months. As such, I built the following three formulas to get my periods identified (the calculation on the spreading of the charges is pretty straightforward once I get this locked down). The frustrating part is in the preview window, these work perfectly but then when I run them in alteryx it blows up. Would love any feedback here:
Month 1
DateTimeTrim([Start Date],'lastofmonth') - I will always have at least one month where the charge needs to go
Month 2
if datetimetrim([End Date], 'lastofmonth')=[Month 1]
then null() else datetimetrim(datetimeadd([Month 1], 1, "month"), 'lastofmonth') endif
Month 3
if isnull([Month 2]) then null() elseif datetimetrim([End Date], 'lastofmonth')=[Month 2] then null() else datetimetrim(datetimeadd([Month 2], 1, "month"), 'lastofmonth') endif
So for example, my first row of data shows a start date of 2020-02-23 and an end date of 2020-02-23. When I run these formulas, I get the following:
Month 1 = 2020-02-29 (this is correct)
Month 2 = 2020-03-31 (this is incorrect - should be null)
Month 3 = 2020-04-30 (this is incorrect - should be null)
