Production demand based on actual output

[Jesper406]

Hello,

I am fairly new to PoweBI and have tried for several weeks but now I am drained of ideas how to fix my problem.

What I want to do should be quite easy, I have data through an API so I can refresh my input whenever I want.

The idea:

I want to have a file which gives recomended operator count based on the demand.

Demand is set for the week and are visualised each pitch (hour).

So far it works great, we can plan the operator count for the following week. Picture below shows how many operators we should be at the production line to meet the demand

Spoiler

The problem:

If we have stop time and were not able to produce as planned I would like to increase the following pitches (hours) based on the lost products.

As well if we have over produced we want to decrease demand for the following pitches (hours).

Takt time = available time / demand

Here you can see a summary of some of the data I have, this should be enough to calculate the takt.

This is what I've tried so far:

Demand Justerat =

VAR DEMAND = CALCULATE(SUM('Pitch LINE'[Demand]),'Pitch LINE'[Week]=WEEKNUM(NOW()))

VAR ACTUAL = CALCULATE(SUM('Pitch LINE'[Actual]),'Pitch LINE'[Week]=WEEKNUM(NOW()))

VAR LEFT= DEMAND-ACTUAL

VAR AVAILABLE TIME= IF('Pitch LINE'[data.pitch_start]>NOW(),CALCULATE(SUM('Pitch LINE'[PLANNED TIME]),'Pitch LINE'[Week]=WEEKNUM(NOW(),21)),0)

VAR F = IFERROR(IF('Pitch LINE'[data.pitch_start]>NOW(),DEMAND-(LEFT/AVAILABLE TIME),BLANK()),BLANK())

RETURN F

Spoiler

DEMAND: Sum the hourly demand if week num is now

ACTUAL: Sum what we produced so far this week

LEFT: What is left to produce

AVAILABLE TIME: If time is greater then now it summarize planned production time

F: Demand-(LEFT/AVAILABLE TIME)

Thanks in advance!

[Anonymous · Accepted solution]

Hi @Jesper406 ,

Since the DAX code is based on context, there doesn't seem to be any problem looking at your DAX snippet alone.

You can try creating these measures.

DEMAND = CALCULATE(SUM('Pitch LINE'[Demand]),FILTER(ALLSELECTED('Pitch LINE'),'Pitch LINE'[Week]=WEEKNUM(NOW()))

ACTUAL = CALCULATE(SUM('Pitch LINE'[Actual]),FILTER(ALLSELECTED('Pitch LINE'),'Pitch LINE'[Week]=WEEKNUM(NOW()))

LEFT= DEMAND-ACTUAL

AVAILABLE TIME= IF(MAX('Pitch LINE'[data.pitch_start])>NOW(),CALCULATE(SUM('Pitch LINE'[PLANNED TIME]),FILTER(ALLSELECTED('Pitch LINE'),'Pitch LINE'[Week]=WEEKNUM(NOW(),21)),0)

Demand Justerat= IFERROR(IF(MAX('Pitch LINE'[data.pitch_start])>NOW(),DEMAND-(LEFT/AVAILABLE TIME),BLANK()),BLANK())

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Jesper406]

Hello,

Thank you for the answer, I've tried this formula and tweaked it a bit but it seems to be working.

I will follow it up during the day and if it works I will accept it as solution 🙂

Regards,

Jesper