Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

3 ACCEPTED SOLUTIONS
Super User
Super User

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Super User

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 10
Super User

should be possible. What's on the Y axis?

Frequent Visitor

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.

Super User
Frequent Visitor

Hi @lbendlin, I have uploaded my pbix example to a dropbox.  I've tried to use ChatGPT to help convert the results to a calculated column though the results don't match up with your measure outputs.  Those are correct.

My end goal is to summarize number of plants by ready date and in what field.  Fields aren't in my example file though and that's not a concern really as that's already available in my working file and is easily sliced to summarize a given date's workload.

I can't add the ready date measure to a slicer, and I'm struggling to add the ready date measure to an x axis.  I'd like to use a clustered column chart to show workload over time, and for daily task setting a table that summarizes number of plants by date and their fields.  Variety isn't that important for that table, that's just the level of granularity that the GDU is set to.

Is a calculated column possible?

https://www.dropbox.com/scl/fi/hnyceeoe34vbh2jt3228c/planted.pbix?rlkey=5nwpajoyby3v64swxlbr8eh44&dl...

Super User

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])
Super User

Frequent Visitor

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.

Frequent Visitor

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?

Super User

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Frequent Visitor

The only thing different was that I had a few varieties that weren't yet planted.  I was just asking your opinion on how you'd handle that, but I just set a filter to not show things not yet planted.

Thank you for your help.

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors