Forum Discussion
Calculate task forecasts with time intelligence based on summing a value
Hello all,
My business case is that I have a nursery. I'm recording GDUs (growing degree units) each day and have a calculated column set up that if I populate my known GDU accumulated by day it uses that value (good for past dates and today). I forecast avg historical GDU into the calculation for future dates, and I have the planting dates for my crops. How can I predict the task date based off of summing the GDUs until the known GDU requirement has been met?
Here is my planting list table of required GDU for task and the date it was planted:
'checklist'
| Variety | GDU required | Planting Date |
| Bell Pepper A | 1401 | 4/15/2024 |
| Bell Pepper B | 1360 | 4/21/2024 |
| Bell Pepper C | 1360 | 4/30/2024 |
| Bell Pepper D | 1314 | 5/3/2024 |
| Bell Pepper E | 1353 | 5/5/2024 |
| Bell Pepper F | 1290 | 5/8/2024 |
| Cantelope A | 1000 | 4/25/2024 |
| Cantelope B | 1060 | 4/30/2024 |
| Watermelon A | 1450 | 4/15/2024 |
| Watermelon B | 1400 | 4/21/2024 |
| Watermelon C | 1425 | 4/30/2024 |
| Tomato A | 1337 | 4/30/2024 |
| Tomato B | 1324 | 5/3/2024 |
| Tomato C | 1312 | 5/5/2024 |
| Tomato D | 1353 | 5/8/2024 |
| Spinach A | 650 | 5/3/2024 |
| Spinach B | 700 | 5/5/2024 |
| Spinach C | 630 | 5/8/2024 |
Here is my GDU table (I know it's long), where each day I input actual GDU, and future dates are the avg/day historically:
'GDU tracking'[CalculatedGDUs] being the column of interest
| Date | Low | High | GDU | CalculatedGDUs |
| 5/1/2024 | 52 | 71 | 11.5 | 11.5 |
| 5/2/2024 | 54 | 66 | 10 | 10 |
| 5/3/2024 | 42 | 73 | 11.5 | 11.5 |
| 5/4/2024 | 48 | 68 | 9 | 9 |
| 5/5/2024 | 40 | 69 | 9.5 | 9.5 |
| 5/6/2024 | 54 | 76 | 15 | 15 |
| 5/7/2024 | 57 | 77 | 17 | 17 |
| 5/8/2024 | 52 | 81 | 16.5 | 16.5 |
| 5/9/2024 | 54 | 65 | 9.5 | 9.5 |
| 5/10/2024 | 51 | 73 | 12 | 12 |
| 5/11/2024 | 46 | 78 | 14 | 14 |
| 5/12/2024 | 49 | 86 | 18 | 18 |
| 5/13/2024 | 61 | 73 | 17 | 17 |
| 5/14/2024 | 0 | 15 | ||
| 5/15/2024 | 0 | 15 | ||
| 5/16/2024 | 0 | 15 | ||
| 5/17/2024 | 0 | 15 | ||
| 5/18/2024 | 0 | 15 | ||
| 5/19/2024 | 0 | 15 | ||
| 5/20/2024 | 0 | 15 | ||
| 5/21/2024 | 0 | 15 | ||
| 5/22/2024 | 0 | 15 | ||
| 5/23/2024 | 0 | 15 | ||
| 5/24/2024 | 0 | 15 | ||
| 5/25/2024 | 0 | 15 | ||
| 5/26/2024 | 0 | 15 | ||
| 5/27/2024 | 0 | 15 | ||
| 5/28/2024 | 0 | 15 | ||
| 5/29/2024 | 0 | 15 | ||
| 5/30/2024 | 0 | 15 | ||
| 5/31/2024 | 0 | 15 | ||
| 6/1/2024 | 0 | 21 | ||
| 6/2/2024 | 0 | 21 | ||
| 6/3/2024 | 0 | 21 | ||
| 6/4/2024 | 0 | 21 | ||
| 6/5/2024 | 0 | 21 | ||
| 6/6/2024 | 0 | 21 | ||
| 6/7/2024 | 0 | 21 | ||
| 6/8/2024 | 0 | 21 | ||
| 6/9/2024 | 0 | 21 | ||
| 6/10/2024 | 0 | 21 | ||
| 6/11/2024 | 0 | 21 | ||
| 6/12/2024 | 0 | 21 | ||
| 6/13/2024 | 0 | 21 | ||
| 6/14/2024 | 0 | 21 | ||
| 6/15/2024 | 0 | 21 | ||
| 6/16/2024 | 0 | 21 | ||
| 6/17/2024 | 0 | 21 | ||
| 6/18/2024 | 0 | 21 | ||
| 6/19/2024 | 0 | 21 | ||
| 6/20/2024 | 0 | 21 | ||
| 6/21/2024 | 0 | 21 | ||
| 6/22/2024 | 0 | 21 | ||
| 6/23/2024 | 0 | 21 | ||
| 6/24/2024 | 0 | 21 | ||
| 6/25/2024 | 0 | 21 | ||
| 6/26/2024 | 0 | 21 | ||
| 6/27/2024 | 0 | 21 | ||
| 6/28/2024 | 0 | 21 | ||
| 6/29/2024 | 0 | 21 | ||
| 6/30/2024 | 0 | 21 | ||
| 7/1/2024 | 0 | 23 | ||
| 7/2/2024 | 0 | 23 | ||
| 7/3/2024 | 0 | 23 | ||
| 7/4/2024 | 0 | 23 | ||
| 7/5/2024 | 0 | 23 | ||
| 7/6/2024 | 0 | 23 | ||
| 7/7/2024 | 0 | 23 | ||
| 7/8/2024 | 0 | 23 | ||
| 7/9/2024 | 0 | 23 | ||
| 7/10/2024 | 0 | 23 | ||
| 7/11/2024 | 0 | 23 | ||
| 7/12/2024 | 0 | 23 | ||
| 7/13/2024 | 0 | 23 | ||
| 7/14/2024 | 0 | 23 | ||
| 7/15/2024 | 0 | 23 | ||
| 7/16/2024 | 0 | 23 | ||
| 7/17/2024 | 0 | 23 | ||
| 7/18/2024 | 0 | 23 | ||
| 7/19/2024 | 0 | 23 | ||
| 7/20/2024 | 0 | 23 | ||
| 7/21/2024 | 0 | 23 | ||
| 7/22/2024 | 0 | 23 | ||
| 7/23/2024 | 0 | 23 | ||
| 7/24/2024 | 0 | 23 | ||
| 7/25/2024 | 0 | 23 | ||
| 7/26/2024 | 0 | 23 | ||
| 7/27/2024 | 0 | 23 | ||
| 7/28/2024 | 0 | 23 | ||
| 7/29/2024 | 0 | 23 | ||
| 7/30/2024 | 0 | 23 | ||
| 7/31/2024 | 0 |
I'd like to forecast, based off of the planting date, adding GDUs in the 'GDU tracking'[CalculatedGDUs] column, the date on which I can expect my crop to flower, hit a milestone, etc.
This would be a calculated column in my 'checklist' table named [ForecastedDate]. Ideally, I'm able to change the value of 'GDU required' in different columns based off of different task needs to generate a to-do list of tasks by date for each crop variety.
Where do I start with this? I feel like I'm using VAR and the GDU required column in a CALCULATE function, though I'm not sure the right approach.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.Here's the Ready Date as a calculated column in the checklist table
Ready Dt = var a = filter('GDU tracking',[Date]>=[Planting Date]) var b = ADDCOLUMNS(a,"c",var d = [Date] return sumx(filter(a,[Date]<=d),[CalculatedGDUs])) var c = filter(b,[c]<=[GDU required]) return maxx(topn(1,c,[Date],DESC),[Date])
10 Replies
Wow thank you!
I've transferred the measures into my working model and I see that in instances of varieties not yet populated with a planting date, it is summing from the beginning of the CalculatedGDUs column. What is the best way to not assign Ready Dates to those items? Would it be in the DAX expression itself or through a Filter on the page/visuals removing null values for planting date?
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
Is it possible to set the measure of ready date to the x axis of a bar chart? I am now looking to aggregate a count of things ready by date via a chart over a table.
should be possible. What's on the Y axis?
I have a measure set to "sum of number of plants" based off of my column of "number of rows" of that variety. "Sum of number of plants" is set to "number of rows" * 20. Categorized by crop in the legend. I'm unable to set "Ready Date" to the x axis to show number of plants (workload) by day. I have these dimensions set in the planted.pbix model you uploaded but the reply function isn't allowing me to upload it to show my example. Not that I can figure out anyway.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
