Forum Discussion

Adamplau's avatar
Adamplau
New Member
3 years ago
Solved

Calculate when start production (excluding weekends)

Hi Guys,
I would like to callculate when I should start production, excluding weekends.
I have two tables Date (mater calendar) and Orders (with [ProductionDueDate] and [ProdactionDurationInDays])
Both tables have inactive Relationship Date[Date] and Orders[ProductionDueDate]

Basicaly, if ProductionDueDate is on Monday and  ProdactionDurationInDays takes 4 business days, I would like to start production on Tuesday week before.

Please help

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Adamplau ,

    You can update the formula of the calculated column [Prod. Start Date (exc. weekends)] as below in the table 'Orders' and check if it can return your expected result... It is not required to create any relationship between the table 'Orders' and 'Date' table.

    Prod. Start Date (exc. weekends) =
VAR DateIdx =
    CALCULATE (
        MAX ( 'Date'[WorkingDayIndex] ),
        FILTER ( 'Date', 'Date'[Date] = 'Orders'[ProductionDueDate] )
    )
VAR NewDateIdx = DateIdx - 'Orders'[ProdactionDurationInDays]
RETURN
    CALCULATE (
        MAX ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[WorkingDayIndex] = NewDateIdx
                && 'Date'[IsWorkingDay] = TRUE()
        )
    )

    Best Regards

7 Replies

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    3 years ago

    Adamplau ,

    I hope the below query would help you to solve the problem,

     

    Starting Day = 
VAR _date =
    SELECTEDVALUE ( 'Table'[ProductionDueDate] )
VAR _duration =
    SELECTEDVALUE ( 'Table'[ProdactionDurationInDays] )
RETURN
    "Start Production on [" & FORMAT ( _date - _duration, "dddd-mmm-d" )&"]"

     

    Thanks,

    • Adamplau's avatar
      Adamplau
      New Member
      3 years ago

      Sorry, but this not what I need. I need to calculate weekends etc. Please read the description.

  • Adamplau's avatar
    Adamplau
    New Member
    3 years ago

    I have this working, however I need to have active relationship between Date and Orders tables.
    How to change it and activate the relationship in the script using USERELATIONSHIP?

    Prod. Start Date (exc. weekends) = 
VAR DateIdx =    
    CALCULATE( 
        MAX( 'Date'[WorkingDayIndex] ),      
        'Orders'[ProductionDueDate] = RELATED('Date'[Date] )
    ) 
VAR NewDateIdx = DateIdx - 'Orders'[ProdactionDurationInDays]

RETURN
CALCULATE(
    MAX('Date'[Date]),
    FILTER(
        ALL('Date'),
        'Date'[WorkingDayIndex] = NewDateIdx 
            && 'Date'[IsWorkingDay] = TRUE()
    )
)

     

    • Arul's avatar
      Arul
      Icon for Super User rankSuper User
      3 years ago

      Adamplau ,

      What is the existing relationship column between these two tables?

      Thanks,

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Adamplau ,

      You can update the formula of the calculated column [Prod. Start Date (exc. weekends)] as below in the table 'Orders' and check if it can return your expected result... It is not required to create any relationship between the table 'Orders' and 'Date' table.

      Prod. Start Date (exc. weekends) =
VAR DateIdx =
    CALCULATE (
        MAX ( 'Date'[WorkingDayIndex] ),
        FILTER ( 'Date', 'Date'[Date] = 'Orders'[ProductionDueDate] )
    )
VAR NewDateIdx = DateIdx - 'Orders'[ProdactionDurationInDays]
RETURN
    CALCULATE (
        MAX ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[WorkingDayIndex] = NewDateIdx
                && 'Date'[IsWorkingDay] = TRUE()
        )
    )

      Best Regards