topic Date Function (DAX) in Developer

Date Function (DAX)

Wecks — Thu, 20 Dec 2018 12:05:46 GMT

I am after a DAX function I can enter into a new collumn into my Dataset that will look at a data field in the same dataset and will give me a date value 2 days from that date.

The kicker is i need it to not include weekends as it needs to be business days only.

Thankyou.

Re: Date Function (DAX)

Greg_Deckler — Thu, 20 Dec 2018 16:38:14 GMT

I believe that something like this will work with everything unless your original date is on a weekend. Could add a check for that if necessary and adjust.

Column = 
VAR __days = 2
VAR __futureDays = __days + 2
VAR __table = FILTER(ADDCOLUMNS(CALENDAR([Date],[Date]+__futureDays),"__weekday",WEEKDAY([Date],2)),[__weekday]<6)
RETURN
IF(COUNTX(__table,[Date])>__days+1,[Date]+2,MAXX(__table,[Date]))

Re: Date Function (DAX)

Wecks — Fri, 21 Dec 2018 08:59:41 GMT

Thanks Greg,

I am not as proficient in Power BI as your good self, i was wondering if you are able to highlight the areas i need to change to fit.

My collumns are HDATE and the Table is called USERS.

Also the date from will always be in the week so that should not be a problem.

Re: Date Function (DAX)

Wecks — Fri, 21 Dec 2018 14:48:30 GMT

Also i forgot to mention HDATE may contain some blanks so this may need to be taken into consideration.

Re: Date Function (DAX)

v-chuncz-msft — Wed, 26 Dec 2018 07:58:08 GMT

@Wecks,

You may check the DAX below.

Column =
VAR d = USERS[HDATE]
RETURN
    MINX (
        FILTER (
            USERS,
            DATEDIFF ( d, USERS[HDATE], DAY ) >= 2
                && NOT ( WEEKDAY ( USERS[HDATE] ) IN { 1, 7 } )
        ),
        USERS[HDATE]
    )

Re: Date Function (DAX)

Wecks — Wed, 02 Jan 2019 10:06:28 GMT

Thanks for this, there is only one issue i am seeing and that is that if a day is monday for example i have a HDate that is the 10th od December it is giving me a date of the 14th which is four days not 2 days passed the HDATE, do you know why that would be?