Forum Discussion

Lazurens's avatar
Lazurens
Frequent Visitor
2 years ago

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

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_DDMMYYYYWeekLignesProductImage W-1Realized CSLImage W-2Comment
24/01/2024 00:002024 - 4L9127030006335.4662910645787.5996568780no image in Week 3
23/01/2024 00:002024 - 4L9127030004120.666422372451.8665213060no image in Week 3
10/01/2024 00:002024 - 2L9127030006598.666275462239.99986723495.3331261099993when aggregate by month = 3495
09/01/2024 00:002024 - 2L9127030009001.06613303210979.7326823923495.3331261099993 
08/01/2024 00:002024 - 2L9127030008191.8661810069969.8660755963495.3331261099993 
05/01/2024 00:002024 - 1L9127030003495.33312610999933143.4664803040no image in Week 52
04/01/2024 00:002024 - 1L9127030004614.3997264323949.866432496  
03/01/2024 00:002024 - 1L9127030004157.999753491791.0665604819999  
21/12/2023 00:002023 - 51L9127030001614.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!

 

"DAX"

2 Replies

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    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's avatar
      Lazurens
      Frequent Visitor
      2 years ago

      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.