Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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] )
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] )
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?
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]))
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.
Also i forgot to mention HDATE may contain some blanks so this may need to be taken into consideration.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |