Adding new columns for forecasted revenue in Alteryx

[alexbowen]

Hi - currently attempting to recreate calculations previously done in excel and not sure about the best method in Alteryx.  I have project data that lists the expected start/end date along with duration (number of months) and forecast revenue.  I'd like to use Alteryx to add in columns for each month in the forecast period and calculates revenue for that engagement in that month.

See below screenshot i'd like to add in the monthly columns (in yellow) and have the monthly revenue for each customer pull into that column (for example in the 7/31/2024 column for customer 1 i'd want to show $5k revenue and so on through the end of 2024).  Also attached excel data for reference.

[apathetichell · Accepted solution]

You skipped a step (or a few steps here). You have $15K. You want $5K in that column. How do we get from $15K to $5K is it because you have 3 months for your project? You need to clarify these steps and that's your workflow. I'd recommend generating rows for your missing months between columns b and c (assuming they are dates in Alteryx). I'd recommend using datetimeadd([columnc],"-1","days") to get the prior month's final date. I'd recommend getting a count of month and dividing out the forecast revenue by count of months - and putting that in my new month rows that I created via generate rows. Then I'd recommend using date time format to change my months into some kind of mm/dd/yyyy format. Then I'd recommend cross tabbing it with my first 4 columns (customer, start, end, total) as key columns - my new month column as a text field as my new column - and my forecasting amount as my value.

Try that. See how it works.

[alexbowen]

Thank you this worked!