Working with dates offers some challenging tasks. Traveling across dates is pretty easy in Power BI. But what happens if someone asks you to add working days into a date? What is the 10th working day from today, or what was the 10th working day before today? Some of these questions are a bit tricky to answer. Let us explore how to deal with such a problem in Power BI.
Solution Overview :
This problem of working days needs a slightly different approach. We need to somehow exclude the non-working dates from our calculations and create a row number or sequence on the remaining dates, then we can move back and forth easily. Rank in Power BI will help us out here.
Rank will help us just like it helped us in Time Intelligence in my last blog. Now, here it will help in traveling across working days. The idea is that we will create a Rank which only considers the WorkDays and then we can move across. But there is a challenge here, it will work fine till the time we get Work Date as Input. But if we get a Non-Work Date as Input, we will not have Rank, and it will fail.
So, what we will do is that we will assign the Last Working Date to Week-End or a Non-Working Day.
Date = CALENDAR(date(2018,01,01), date(2021,12,31))
Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)
Now use it:
Plus 10 Days = var _max =maxx(ALLSELECTED('Date'),'Date'[Work Date cont Rank])
CALCULATE(Min('Date'[Date]),filter(ALL('Date'),'Date'[Work Date Rank] =_max+10))
Rolling Last 10 Days = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Work Date cont Rank]>=min('Date'[Work Date cont Rank])-10
&& 'Date'[Work Date cont Rank]<=max('Date'[Work Date cont Rank])))