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 th...
"DAX"
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.