Need Help with Implementing a Complex Business Rule for Production Planning and Monitoring

[Lazurens]

I'm currently working on a project that involves managing and monitoring production plans. I have a specific business rule that I need to implement, but I'm encountering some challenges in getting the logic right. I would greatly appreciate any guidance or insights you could offer. Here's the business rule:

Business Rule : 

On the Plan Adherence page (example: production week 4 – the planned is the photo of the weekly schedule for Sunday of calendar week 3)

On the W-1 Completion Rate page (example: Production week 4 – the planned is the photo of the weekly schedule for Sunday of calendar week 3)

On the W-2 Completion Rate page (example: Production week 4 – the planned is the Sunday photo of calendar week 2)

On the Daily monitoring page (example: production week 4 – the planned is the photo of the weekly schedule for Sunday of calendar week 3)

I am trying to ensure that the 'planned' data reflects the correct photo of the weekly schedule based on the production week in question. However, I'm having trouble aligning the data correctly and ensuring that the right 'planned' data is displayed on each respective page.

Exemple of Data : hoped result in "Image W-2"
Calculation : Realized CSL - Image W-2

DT_DDMMYYYY	Week	Lignes	Product	Image W-1	Realized CSL	Image W-2	Comment
24/01/2024 00:00	2024 - 4	L	912703000	6335.466291064	5787.599656878	0	no image in Week 3
23/01/2024 00:00	2024 - 4	L	912703000	4120.66642237	2451.866521306	0	no image in Week 3
10/01/2024 00:00	2024 - 2	L	912703000	6598.66627546	2239.9998672	3495.3331261099993	when aggregate by month = 3495
09/01/2024 00:00	2024 - 2	L	912703000	9001.066133032	10979.732682392	3495.3331261099993
08/01/2024 00:00	2024 - 2	L	912703000	8191.866181006	9969.866075596	3495.3331261099993
05/01/2024 00:00	2024 - 1	L	912703000	3495.3331261099993	3143.466480304	0	no image in Week 52
04/01/2024 00:00	2024 - 1	L	912703000	4614.399726432	3949.866432496
03/01/2024 00:00	2024 - 1	L	912703000	4157.99975349	1791.0665604819999
21/12/2023 00:00	2023 - 51	L	912703000	1614.66657094

I would be incredibly grateful for any advice on how to structure my queries or logic to accurately reflect these rules. Specifically, I'm looking for guidance on:

• Ensuring that the correct 'planned' data (from the appropriate week's photo) is matched with the current production week.
• Handling cases where the SKU product is not found in the planning photo (should return 0 for planned quantity and a value for the Product field).
  
  SCHEMA MODEL : 

CALENDAR[DT_DDMMYYYY] -> FACT_PRODUCT_MANUFACTURING[PLANNED_IMAGE_DATE]
CALENDAR[Week]

PRODUCTION_LINE[PRODUCTION_LINE]

PRODUCT[PRODUCT_ID]
FACT_PRODUCT_MANUFACTURING[IMG_WEEK_QTY] : Image W-1

FACT_PRODUCT_MANUFACTURING[TOTAL_QTY] : Realized CSL

Thank you so much for your time and help!

[123abc]

To implement the described business rule in Power BI DAX, you'll need to consider the following steps:

1. Identify the Current Production Week: Determine the current production week based on the calendar date.

2. Retrieve the Planned Data: Retrieve the planned data (Image W-1, Image W-2, etc.) based on the current production week and the appropriate week's photo.

3. Handle Missing SKUs: If the SKU product is not found in the planning photo, return 0 for the planned quantity and a value for the Product field.

Here's a general guideline on how to structure your DAX measures:

Measure 1: Current Production Week

This measure identifies the current production week based on the calendar date.

CurrentProductionWeek =
VAR CurrentDate = TODAY() // Or use any specific date if needed
RETURN
WEEKNUM(CurrentDate)

Measure 2: Retrieve Planned Data

This measure retrieves the planned data based on the current production week and the appropriate week's photo.

PlannedData =
VAR CurrentWeek = [CurrentProductionWeek]
VAR PlannedWeek =
SWITCH(
TRUE(),
CurrentWeek = 'FACT_PRODUCT_MANUFACTURING'[Week], 'FACT_PRODUCT_MANUFACTURING'[PLANNED_IMAGE_DATE],
CurrentWeek - 'FACT_PRODUCT_MANUFACTURING'[Week] = 1, 'FACT_PRODUCT_MANUFACTURING'[PLANNED_IMAGE_DATE],
CurrentWeek - 'FACT_PRODUCT_MANUFACTURING'[Week] = 2, 'FACT_PRODUCT_MANUFACTURING'[PLANNED_IMAGE_DATE],
BLANK()
)
RETURN
PlannedWeek

Measure 3: Handle Missing SKUs

This measure handles cases where the SKU product is not found in the planning photo.

PlannedQuantity =
VAR CurrentSKU = SELECTEDVALUE('PRODUCT'[PRODUCT_ID])
RETURN
IF(
ISBLANK(CurrentSKU),
0,
// Your logic to retrieve planned quantity based on CurrentSKU and PlannedData
)

These measures should be adapted to fit into your Power BI model and data structure. You'll need to adjust the logic for retrieving planned quantities based on your actual data model and relationships between tables.

Remember to replace the placeholders with actual column names and table relationships from your schema model. Testing these measures with your data should help in fine-tuning and ensuring they produce the desired results.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Lazurens]

I gotten inspired by your method: 

This measure presents correctly the desired output, only in a table that has 

DIM_CALENDAR_MES[DT_DDMMYYYY] in it, when I remove this field from the visual I get nothing

SecondLastValue = 
VAR CurrentWeek = SELECTEDVALUE(DIM_CALENDAR_MES[WeeksGap])
VAR PreviousWeek = CurrentWeek - 1
VAR MaxDateForPreviousWeek = CALCULATE(
    LASTDATE(FACT_PRODUCT_MANUFACTURING[PLANNED_IMAGE_DATE]),
    ALL(DIM_CALENDAR_MES),
    DIM_CALENDAR_MES[WeeksGap] = PreviousWeek
)
VAR Result = CALCULATE(
    SUMX(
        FACT_PRODUCT_MANUFACTURING,
        FACT_PRODUCT_MANUFACTURING[QTYTOPRODUCE_WEEK_IMG] * FACT_PRODUCT_MANUFACTURING[CONVERSION_FACTOR]
    ),
    FILTER(
        ALL(DIM_CALENDAR_MES),
        DIM_CALENDAR_MES[DT_DDMMYYYY] = MaxDateForPreviousWeek
    )
)
RETURN
IF(ISBLANK(Result), 0, Result)

 
I should display this by Week view not by date I just displayed the date to make sure of the result. 

Any help is appreciated.