Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculated dates and days columns

Hi Ashish_Mathur ,

Can you please help with creating the below highlighted calculated columns derived from the data entered in the first 4 columns. The example also is shown to better explain the desired outcome below

 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous ,

     

    New a calendar table first.

     

    calendar table =
FILTER (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 7, 1 ) ),
        "Weekday", WEEKDAY ( [Date], 2 )
    ),
    NOT [Weekday] IN { 6, 7 }
)

     

     

    In fact data table, create calculated columns as below.

     

    Expected Delivery date =
VAR current_rank =
    CALCULATE (
        MAX ( 'calendar table'[Rank] ),
        FILTER (
            'calendar table',
            'calendar table'[Date] = EARLIER ( Test_1[Email Received Date] )
        )
    )
RETURN
    LOOKUPVALUE (
        'calendar table'[Date],
        'calendar table'[Rank], current_rank + Test_1[SLA] - 1
    )

Actual days =
CALCULATE (
    COUNTROWS ( 'calendar table' ),
    FILTER (
        'calendar table',
        AND (
            'calendar table'[Date] >= Test_1[Email Received Date],
            'calendar table'[Date] <= Test_1[Actual Delivery date]
        )
    )
)

SLA Met? = IF(Test_1[Actual Delivery date]<=Test_1[Expected Delivery date],"Met","Not Met")

     

    Best regards,

    Yuliana Gu

     

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    This would invole using the equivalent of the WORKDAY function in MS Excel.  Unfortunately, there is no such function in either the Power Query or the PowerPivot.  Someone else will help you with this question please.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

     

    In above example, why "Expected delivery date" is "17 May 2019"? Actually, formula 

    "DATEADD(Test_1[Email Received Date].[Date],Test_1[SLA],DAY)" returns "18 May 2019".
     
    Besides, when calculating "Actual Days", what do you mean "Should count the # of days until column D is updated"?
     
    Best regards,
    Yuliana Gu
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-yulgu-msft ,

       

      Thank you very much for taking this up. 

       

      About your first query, my apologies for any confusion, but for the example given, the expected delivery date should count 10 (per the SLA defined) working days from the Received date and give the result. Hence, if the received date was May 8th, the expected delivery date should be 21st May (excluding 11th 12th 18th and 19th.. the weekend).

      The simple calculation here is Work Type+Respective 'SLA' (workdays only) = 'Expected Delivery Date'

       

      Similarly, for each, work type the expected date should appear per their defined turn around time (given in the SLA column).  Hope this helps?

       

      About the actual days = Actual Delivery Date - Email Received Date. 

      Expectation- should exclude weekends in the count of days. 

       

      On SLA Met? = When 'Actual Delivery Date' is equal or lesser than 'Expected Delivery Date' ..MET. When Greater than 'Expected Delivery Date' should be NOT MET.

       

      Hope this clarifies your doubts. 

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous ,

         

        New a calendar table first.

         

        calendar table =
FILTER (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 7, 1 ) ),
        "Weekday", WEEKDAY ( [Date], 2 )
    ),
    NOT [Weekday] IN { 6, 7 }
)

         

         

        In fact data table, create calculated columns as below.

         

        Expected Delivery date =
VAR current_rank =
    CALCULATE (
        MAX ( 'calendar table'[Rank] ),
        FILTER (
            'calendar table',
            'calendar table'[Date] = EARLIER ( Test_1[Email Received Date] )
        )
    )
RETURN
    LOOKUPVALUE (
        'calendar table'[Date],
        'calendar table'[Rank], current_rank + Test_1[SLA] - 1
    )

Actual days =
CALCULATE (
    COUNTROWS ( 'calendar table' ),
    FILTER (
        'calendar table',
        AND (
            'calendar table'[Date] >= Test_1[Email Received Date],
            'calendar table'[Date] <= Test_1[Actual Delivery date]
        )
    )
)

SLA Met? = IF(Test_1[Actual Delivery date]<=Test_1[Expected Delivery date],"Met","Not Met")

         

        Best regards,

        Yuliana Gu