Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Lazurens
Frequent Visitor

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!

 

2 REPLIES 2
123abc
Community Champion
Community Champion

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.

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. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.