Forecasting the Workload for an area for next week

[Krays23]

HI,

I`m trying to figure out how i can project forwards the work completed in one area of my business to the next areas planned work load for the following week.

See attached screen shot and PBIX i have 7 areas in my business that process parts one after another week to week. Each area is shown with a sequence number.

As an example ill use number 1 process CCA.

In week 36 the CCA actual was 254 parts the following week, week 37 i want those 254 parts to be the plan for sequence 2 Autoclave and then in week 38 whatever the Autoclave finishes should be the plan for CNC area sequence 3.

This trend should continue for all areas that what ever is finished should be the plan for the next sequenced area the following week.

OTD Test Power Bi Planning.pbix (973.4 KB)

Appreciate your help

Dan

[Melissa]

Hi Dan,

I’ve made the following changes.

1. added a WeekOffset to your calendar table
   

   

2. changed the Sequence data type from text to number
   

3. created a Plan v2 measure (not sure why “preformatted text” doesn’t work here…)

   Plan v2 =
   VAR myWeek =
   CALCULATE( MAX( ‘Date Table’[WeekOffset] )|
   FILTER( ALL( ‘Date Table’)|
   ‘Date Table’[Week Number] = SELECTEDVALUE( ‘Date Table’[Week Number] ) &&
   ‘Date Table’[Year] = SELECTEDVALUE(‘Date Table’[Year] )
   )
   )
   VAR mySequence = SELECTEDVALUE( ‘OTD Area Definitions’[Sequence] )
   RETURN

   CALCULATE( [Actual] |
   FILTER( ALL(‘Date Table’) |
   ‘Date Table’[WeekOffset] = myWeek -1
   )|
   FILTER( ALL( ‘OTD Area Definitions’ )|
   ‘OTD Area Definitions’[Sequence] = mySequence -1
   )
   )

I have a question. How do you want to deal with the workload for Sequence 1 CCA in following weeks, should that be Blank or Actual ?

Anyway this is the result for now. OTD Test Power Bi Planning.pbix (980.4 KB)

[EnterpriseDNA]

Hi @Krays23, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks

[BrianJ]

@Melissa,

I had the same problems with the preformatted text, and @Nick_M figured it out. If you put a blank line between the first line ( measure name = ) and the start of your DAX code, the preformatting option will work perfectly.

• Brian

[Krays23]

Hey Melissa

Thanks thats awesome.

Yeah CCA is an issue as i said to the planning team Power BI is not excel and it needs to get the data from somewhere.

For now can you set the CCA to match what it actual achieved that week?

Dan

[Melissa]

Hi Dan,

I’ve added a Measure [Plan v3], so it now shows [Actual] where the Sequence = 1
Just wrapped it in an IF statement, so you can change that easily should the need arise…

Here’s the file: OTD Test Power Bi Planning.pbix (987.6 KB)

Plan v3 = 

VAR myWeek = 
    CALCULATE( MAX( 'Date Table'[WeekOffset] ),
        FILTER( ALL( 'Date Table'),
            'Date Table'[Week Number] = SELECTEDVALUE( 'Date Table'[Week Number] ) &&
            'Date Table'[Year] = SELECTEDVALUE('Date Table'[Year] ) 
        ) 
    )
VAR mySequence = SELECTEDVALUE( 'OTD Area Definitions'[Sequence] )
RETURN

IF( mySequence = 1, [Actual],
    CALCULATE( [Actual],
        FILTER( ALL('Date Table'),
        'Date Table'[WeekOffset] = myWeek -1
        ),
        FILTER( ALL( 'OTD Area Definitions' ),
            'OTD Area Definitions'[Sequence] = mySequence -1    
        )
    )
)

.
@BrianJ, thank you for your feedback!

[EnterpriseDNA]

Hi @Krays23, we’ve noticed that no response has been received from you since December 16, 2019. We just want to check if you still need further help on this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arises.

[Krays23]

Many thanks that perfect