working days of oldest email

[Trebor84]

Hi,

Hoping someone can help me please.

I have a table of email received dates and the folder they are currently in, I.e inbox or sent. 

I need to find a way to calculate the working days (min-fri) outstanding (also excluding bank holidays) of the oldest email in the inbox folder and display this date in a card visual.

I have included a separate table listing all uk bank holidays.

I have also included a basic calendar table, but understand I may need to include the weekday function in the table to be able to accomplish this.

I have included a sample file.

working day test.pbix 

Thanks

[AlB · Accepted solution]

Hi @Trebor84 

One way would be to

1. Add a new column to the code that generates your calendar table to track actual working days:

    "IsWorkingDay",
        IF (
            NOT [Date] IN DISTINCT ( holidays[UK BANK HOLIDAYS] )
                    && NOT WEEKDAY ( [Date], 2 ) IN { 6, 7 },
            "YES",
            "NO"
        )

 2. Create the measure and place it in a card visual:

WorkingDaysOutstanding = 
VAR oldestInboxDate_ = CALCULATE ( MIN ( emails[email sent] ), emails[folder] = "Inbox" )
RETURN
    CALCULATE (
        COUNT ( 'Calendar'[Date] ),
        DATESBETWEEN ( 'Calendar'[Date], oldestInboxDate_, TODAY () ),
        'Calendar'[IsWorkingDay] = "YES"
    ) - 1

 See it all at work in the attached file.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[v-yiruan-msft · Accepted solution]

Hi @Trebor84 ,

I updated your sample pbix file, please find the attachment for the details.

First, you can create a calculated column in calendar table to judge the date is working date or not:

Isworkingday = 
VAR _tab =
    CALCULATETABLE ( VALUES ( 'holidays'[UK BANK HOLIDAYS] ) )
RETURN
    IF ( 'Calendar'[Date] IN _tab || WEEKDAY ( 'Calendar'[Date], 2 ) > 5, 0, 1 )

Then create a measure to get the working days

Working days = 
VAR _oldestdate =
    CALCULATE ( MIN ( 'emails'[email sent] ), 'emails'[folder] = "inbox" )
RETURN
    CALCULATE (
        COUNTROWS ( FILTER ( 'Calendar', 'Calendar'[Isworkingday] = 1 ) ),
        DATESBETWEEN ( 'Calendar'[Date], _oldestdate, TODAY () )
    )

If the above one is wrong, please provide the correct calculation logic and expected result with examples. Thank you.

Best Regards

[AlB · Accepted solution]

Hi @Trebor84 

One way would be to

1. Add a new column to the code that generates your calendar table to track actual working days:

    "IsWorkingDay",
        IF (
            NOT [Date] IN DISTINCT ( holidays[UK BANK HOLIDAYS] )
                    && NOT WEEKDAY ( [Date], 2 ) IN { 6, 7 },
            "YES",
            "NO"
        )

 2. Create the measure and place it in a card visual:

WorkingDaysOutstanding = 
VAR oldestInboxDate_ = CALCULATE ( MIN ( emails[email sent] ), emails[folder] = "Inbox" )
RETURN
    CALCULATE (
        COUNT ( 'Calendar'[Date] ),
        DATESBETWEEN ( 'Calendar'[Date], oldestInboxDate_, TODAY () ),
        'Calendar'[IsWorkingDay] = "YES"
    ) - 1

 See it all at work in the attached file.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.