Calculate previous working day (or, "calculate +/- WORK days from given date)

[mrqs]

Guys

I've been looking through other topics with a similar keywords, but nothing seems to match what I need (one solution even suggested something, that looks like simple calendarday +/- n days).

I need to mark/filter the records, where "shipping date" field equals to today - 1 WORKING day (not a calendar day).

So, if today is Monday, I need only these from last Friday. If it's Tuesday - I need Monday's records.

I can then set a filter to only show records with that mark.

Is there any DAX or so? So far, the only possibility for me seems to be something like:

if today=Monday then 

     date=today-3

else

      if today = Sunday then

          date=today-2

       else

           date=today-1

       end if

end if

For Monday, Sunday, Saturday it should always point Friday, otherwise just date-1 day

Thanks for any advise

[DoubleJ]

Hi

How about you add a column with this DAX formula (you might have to change semicolons with commas):

PrevWorkingDAy = 
	MAXX(
		FILTER('Calendar';'Calendar'[Date] < EARLIER('Calendar'[Date])
			&& WEEKDAY('Calendar'[Date];3) <5); /*0=Monday, 6=Sunday --> hence < 5 is Monday to Friday*/
		'Calendar'[Date] 
        )

This results in this. In my environment the dates are in "DD.MM.YYY" format:

You might have to consider public holiday. 

Hope this helps!

JJ

[Anonymous]

Hi @DoubleJ that worked perfect for me. However, I need to include holdays in my "PrevWorkingDay" column. Any example on how to do it? I´m very new in dax formulas.

PrevWorkingDAy = 
	MAXX(
		FILTER('Calendar';'Calendar'[Date] < EARLIER('Calendar'[Date])
			&& WEEKDAY('Calendar'[Date];3) <5); /*0=Monday, 6=Sunday --> hence < 5 is Monday to Friday*/
		'Calendar'[Date] 
        )

Thanks a lot. 

[mrqs]

Thanks JJ!

Well.. I seem to be touched by the "Direct Query Course".

Yeah, I do use direct query as my data is stored on SQL server connected via the on premise gateway.

So, the message I get is: "Function 'MAXX' is not allowed as part of calculated column DAX expressions on DirectQuery models."

I'm stuck (otherwise, your solution would work for me!)

[v-caliao-msft]

@mrqs,

Since you use direct query to load your data and your source table a SQL Server table. You could achieve this in your source table, to get previous working day, you could use the T-SQL below.
SELECT DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE())
                                       WHEN 'Sunday' THEN -2
                                       WHEN 'Monday' THEN -3
                                       ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))

Besides, here is blog which about how to get previous business day for Non-Working days, please refer to the link below.
https://social.technet.microsoft.com/wiki/contents/articles/37629.powerbi-power-query-report-previou...

Regards,

Charlie Liao

[Anonymous]

How can I achieve the same when I have a holiday in between the days

if Friday was a holiday then I should be seeing Thursdays data,

similarly if Monday was a holiday I need to see Fridays data

How to achieve this?

@v-calis @v-caliao-msft @mrqs