LastWorkDate QlikView Function to Power BI

[elxaxe]

Hi,

I'm migrating a Qliview dashboard to MS Power BI but there are some functions that Power BI does not have. One that is being used in the QV dashboard is LastWorkDate(start_date, no_of_days [, holidays]). That function returns the last working day after adding no_of_days to the start_date. Example:

LastWorkDate('19/10/2017', 2) // Will return '23/10/2017' because the 20th and 21st of october are not working days.

Also, if you specify holidays period, those days woyld be taken as not working days.

I need to convert expressions like:

IF(LastWorkDate(SHIPPING_DATE, 4)) < Floor($(varToday), "OPEN", "CLOSED")

But after searching in the forums and the Internet I cannot figure out how to put that conditional in a DAX expression for a Calculated Column.

If only we could create our custom functions...

THANKS IN ADVANCE!

[v-ljerr-msft]

Hi @elxaxe,

First, you'll need to create an individual and continuous Date table if you don't have it yet.

Date = CALENDAR("2017/10/1","2017/12/31")

And add a column to the Date table that is 1 for workdays and 0 for non-workdays:

IsWorkday = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

Then the DAX formula to convert LastWorkDate(SHIPPING_DATE, 4) in a calculate column should be like below. 

=
VAR CURRENT_SHIPPING_DATE = SHIPPING_DATE
RETURN
    CALCULATE (
        MIN ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date]
                >= CURRENT_SHIPPING_DATE + 4
                && 'Date'[IsWorkday] = 1
        )
    )

Regards

[elxaxe]

Thanks for the answer, @v-ljerr-msft !

Just a question from a noob like me: why are you using a variable? Wouldn't be the same if I write:

=
    CALCULATE (
        MIN ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date]
                >= SHIPPING_DATE + 4
                && 'Date'[IsWorkday] = 1
        )
    )

[v-ljerr-msft]

Hi @elxaxe,

Thanks for point that out! The formulas are the same, and it's simpler to use your formula in this scenario. It seems to be that I have been used to use the VAR functions to write DAX formulas. 

Regards

[elxaxe · Accepted solution]

Hi,

Instead of adding 4 days I've tried to add only 1 to the formula. But my surprise is that if use it for the day '23/10/2017', it returns '25/10/2017' instead of '24/10/2017'.

I've found the solution:

aux_LastWorkDate(SHIPPING_DATE) = 
CALCULATE(
    MIN(MyCalendar[Date]);
    FILTER(
        ALL(MyCalendar);
        MyCalendar[Date] >= (SHIPPING_DATE.[Date] + 1)
        && MyCalendar[isWorkDay] = 1
    )
)

I don't know why this works as the date should is the same.