Hello,
I was wondering if something like this would be possible... I have data that includes only inception to date numbers, but I need a calculated field in my query that will solve for Month to date. For instance, (September 2017 ITD) - (August 2017 ITD) = (September 2017 MTD).
This is how my data looks:
Company Date (yyyymm) Gross Profit ITD Apple Co. 201706 200,000.00 Apple Co. 201707 220,000.00 Apple Co. 201708 240,000.00 Apple Co. 201709 260,000.00 Grape Co. 201706 100,000.00 Grape Co. 201707 105,000.00 Grape Co. 201708 107,000.00 Grape Co. 201709 115,000.00
In this example, we can tell that the month to date numbers would be 20,000 each month for Apple Co.
The new table would look like this:
Company Date (yyyymm) Gross Profit MTD Gross Profit ITD Apple Co. 201706 200,000.00 Apple Co. 201707 20,000.00 220,000.00 Apple Co. 201708 20,000.00 240,000.00 Apple Co. 201709 20,000.00 260,000.00 Grape Co. 201706 100,000.00 Grape Co. 201707 5,000.00 105,000.00 Grape Co. 201708 2,000.00 107,000.00 Grape Co. 201709 8,000.00 115,000.00
I'm thinking that a dlookup function could help here, but I'm not sure. It's difficult because there are several companies that could be scattered in the data, and it might incorrectly take the current ITD value and subtract a previous ITD value from a different company. Any thoughts on how to tackle this? Thanks!