Forecast number of days work based an average over a number of days

[CraigBlackman]

Hi All,

Hoping for a little guidance here if possible.

My reports presently tells me how many items we have completed per day. We have another total which is work to be completed or backlog for a better phrase.

What I would like to do is take a date range, lets say the last 5 days, look at the total completed and then based on that figure calculate how many days work we have. If the date could be dynamic based on what date are selected on the report that would be even better.

Really appreciaet any help provided.

Thanks

Craig

[jessicammoss]

Could you please provide some sample data of what you have today and what you would like the output to be? Specifically, the relationship between what you've done versus what you will do in the future (is it based on hours, number of items, etc?).

Thanks,

Jessica

[CraigBlackman]

Hi @jessicammoss

Sorry for the late reply.

In answer to your question, keeping it simple I have 2 tables and the following fields

works_order

works_order.wo_id

works_order.wo_built_datetime

works_order_total

works_order_total.wo_id

works_order_total.quantity_built

works_order_total.quantity_unbuilt

ATM I am summing by day the quantity built. If we again keep is simple and just look at the average over the last 5 days and then divide that by whats unbuilt as a total and return the number of days it will take to clear that.

Hope that makes sense.

Thanks for your help.

[v-ljerr-msft]

Hi @CraigBlackman,

If I understand you correctly, you should be able to follow steps below to create a measure to calculate the forecast number of days according to your selections on the date Slicer in this scenario.

1. Create an individual Date table if you don't have one yet(using CALENDAR or CALENDARAUTO Function (DAX)). And create a relationship between your "works_order" table and the created Date table with the date columns.

2. Assume the Date table is called "Date", then the formula to create the measure should like below(You can use a Slicer with date column from the Date table, and the measure can calculate forecast number of days according to your selections).

Forecast number of days =
CALCULATE (
    SUM ( works_order_total[quantity_unbuilt] ) / COUNTROWS ( Date[Date] )
)

Regards