cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Backwards calculation of remaining production order hours

Hi

I'm doing some productions planning, and i need show how many hours of production is planned for each day.

Example Production 001108 is budgeted with 100 hours and needs to be done at 24-06-2019, as seen from the left table.  The right table indicates, that 53,8 hours have already been clocked. So i need to spread out 46,2 hours over (46,2/7,4) = 7 days, aka. 7,4; 7,4; 7,4; 7,4; 7,4; 7,4, 1,8

Both tables are already connected to a common lookup table for a calender and for a production order

Highlighted
Super User III

## Re: Backwards calculation of remaining production order hours

Hi, @Nij

I have created a report by you specs and the sample data you have provided. Based on the sample data I have created a few 1-to-1 relationships. If this is not how your data is, you would have to make som changes in some of the calculated columns. It is a bit messy to skip weekends, so some more testing with more data should probably be done. But that I leave to you 🙂

hours =
VAR weekendays =
CALCULATE (
SUM ( dates[IsWeekend] );
FILTER (
ALL ( dates );
dates[Date]
> MIN ( 'Production Orders'[EndDate] )
- MIN ( 'Production Orders'[Number of days left] )
&& dates[Date] <= MIN ( 'Production Orders'[EndDate] )
)
)
VAR startDateIsWeekend1 =
IF (
COUNTROWS (
FILTER (
ALL ( dates );
dates[Date]
= MIN ( 'Production Orders'[EndDate] )
- MIN ( 'Production Orders'[Number of days left] ) - weekendays
&& dates[IsWeekend] = 1
)
) > 0;
1;
BLANK ()
)
VAR startDateIsWeekend2 =
IF (
COUNTROWS (
FILTER (
ALL ( dates );
dates[Date]
= MIN ( 'Production Orders'[EndDate] )
- MIN ( 'Production Orders'[Number of days left] ) - weekendays - 1
&& dates[IsWeekend] = 1
)
) > 0;
1;
BLANK ()
)
+ IF ( ISBLANK ( startDateIsWeekend1 ); 1; 0 )
RETURN
SWITCH (
TRUE ();
MIN ( dates[Date] )
> MIN ( 'Production Orders'[EndDate] )
- MIN ( 'Production Orders'[Number of days left] ) - weekendays - startDateIsWeekend1
&& MIN ( dates[Date] ) <= MIN ( 'Production Orders'[EndDate] ); 7,4;
MIN ( dates[Date] )
= MIN ( 'Production Orders'[EndDate] )
- MIN ( 'Production Orders'[Number of days left] ) - weekendays - startDateIsWeekend1 - startDateIsWeekend2; SUM ( [Number of hours left] );
BLANK ()
)

cheers,

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors