Calculated dates and days columns

[Anonymous]

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

[v-yulgu-msft]

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]

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 · Accepted solution]

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

[Anonymous]

Hi @v-yulgu-msft ,

This is Superlative!! Thanks a ton for this help.

I think I am almost there to get this implemented. I tried to create the Rank column but I couldn't it exactly what's needed.  How did you do that please?

[v-yulgu-msft]

Hi @Anonymous ,

Rank = RANKX('calendar table',[Date],,ASC,Dense)

Best regards,

Yuliana Gu

[Ashish_Mathur]

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.