Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
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!
To implement the described business rule in Power BI DAX, you'll need to consider the following steps:
Identify the Current Production Week: Determine the current production week based on the calendar date.
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.
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:
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)
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
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
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.
User | Count |
---|---|
20 | |
17 | |
15 | |
11 | |
7 |
User | Count |
---|---|
28 | |
27 | |
13 | |
12 | |
12 |