Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

amitchandak
Super User
‎06-28-2020 07:30 PM

Travelling Across Workdays - Decoding Date and Calendar 4-5 - Power BI Turning 5 Celebration Series

Problem Description:

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.

 

Solution:

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.

 

 

Calendar:

 

 

 

 

Date = CALENDAR(date(2018,01,01), date(2021,12,31))

 

 

 

 

 

WorkDay Columns:

 

 

 

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])
return
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])))

 

 

 

 

 

You can also find a pbix attached to this blog.

 

My Previous Blogs -

Week Is Not So Weak,

Date Difference Across Table- Direct Query Mode, Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard Time Periods and Comparing Data Across Date Ranges
Connect on LinkedIn

travelWorkDay.pbix
Comments
Advocate IV
‎05-03-2021 11:18 AM

This doesn't really show how to use it in a realistic scenario, like adding working days to a date column in a fact table vs the date column in the Date table. (I assume the table is called "Date" because the term "Date" is used in different ways throughout your example).

0
Super User
‎03-01-2022 10:45 PM

New column in sales table

 

Sale Date +10 New column =
var _min = MinX(filter('Date','Date'[Date] = sales[Date]), [Work Date cont Rank] ) +10
return
Minx(filter('Date', [Work Date cont Rank] = _min), 'Date'[Date])

 

or

Sale Date +10 New column =

var _min = related( [Work Date cont Rank] ) +10
return
Minx(filter('Date', [Work Date cont Rank] = _min), 'Date'[Date])

0