Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
irnm8dn
Post Prodigy
Post Prodigy

Day Count Business Days Between Two Dates

I am currently using the following function for Day Count:

 

Day Count of Drop = SWITCH(
TRUE(),
'KPI Dashboard AD'[Campaign Creation Date]<'KPI Dashboard AD'[Start Date], DATEDIFF('KPI Dashboard AD'[Campaign Creation Date],'KPI Dashboard AD'[Start Date],DAY),
'KPI Dashboard AD'[Start Date]>'KPI Dashboard AD'[Campaign Creation Date], DATEDIFF('KPI Dashboard AD'[Start Date],[Days Between Drop Creation and Start],DAY)*-1,
0
)

 

A use case has come up that I would like to calculate business days between creation and start date, so it will more properly represent the "time in process".

 

I am unsure if will need to modify or take a different approach to do the caluclation.

 

Thanks in advance for the help!

 

1 ACCEPTED SOLUTION

I intended it as a measure.

 

The culprit is this statement:

 

Business Day Value = if('Date'[DayOfWeek]="Monday"||
'Date'[DayOfWeek]="Tuesday"||
'Date'[DayOfWeek]="Wednesday"||
'Date'[DayOfWeek]="Thursday"||
'Date'[DayOfWeek]="Friday",
"1",
"0")

This statement results in a string of either "1" or "0". Change this to:

Business Day Value = if('Date'[DayOfWeek]="Monday"||
                        'Date'[DayOfWeek]="Tuesday"||
                        'Date'[DayOfWeek]="Wednesday"||
                        'Date'[DayOfWeek]="Thursday"||
                        'Date'[DayOfWeek]="Friday",
                        1,
                        0)

and make sure that the Business Day Value column in your calendar table is of type Whole Number. That should do it.

 

View solution in original post

12 REPLIES 12
v-jiascu-msft
Employee
Employee

Hi @irnm8dn,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I have not gotten a solution that works yet.

erik_tarnvik
Solution Specialist
Solution Specialist

Hi @irnm8dn,

do you have a calendar table in your model? If you do, create a custom column [Workday] in the calendar table that contains 1 for days that are workdays and 0 otherwise. Then change the [Day Count of Drop] to sum up the ones and zeros between the dates, i.e. CALCULATE(SUM(Dates[Workday], FILTER(Dates, xxx[Creation Date] >= Dates[Date] && xxx[Start Date] < Dates[Date]). You still need the test and multiply by -1 to get that part right.

@erik_tarnvik

 

Thanks.  I created a column and used the following statement:

 

Business Day Value = if('Date'[DayOfWeek]="Monday"||'Date'[DayOfWeek]="Tuesday"||'Date'[DayOfWeek]="Wednesday"||'Date'[DayOfWeek]="Thursday"||'Date'[DayOfWeek]="Friday","1","0")

 

I am a little foggy on these two parts:

 

1.  CALCULATE(SUM(Dates[Workday], FILTER(Dates, xxx[Creation Date] >= Dates[Date] && xxx[Start Date] < Dates[Date])

     a. Please break down this statement so I understand the functions  

2.   Not sure what you mean about needing a test and multuply by -1

 

Thanks!

 

Can you help me understand with a little more detail?

 

 

 

 

@irnm8dn

 

 

Sure! I think you are heading in the right direction, the Business Day Value column is exactly what i had in mind.  

 

Regarding your two questions, I was referring to your Switch statement where you want to account for the case where you want a negative value as a result, in that case you need to compare the two dates to see which is smaller than the other and account for that. The CALCULATE simply filters the calendar table to contain only the dates between your cutoffs and then sums up the working dates. See below for an example, you may need to adjust somewhat, I haven't tested this but it should give you the right idea. 

 

Day Count of Drop = 
IF('KPI Dashboard AD'[Campaign Creation Date]<'KPI Dashboard AD'[Start Date], 
CALCULATE(Sum('Date'[Business Day Value]),
FILTER('Date', 'Date'[DateColumn] >= 'KPI Dashboard AD'[Campaign Creation Date] &&
'Date'[DateColumn] < 'KPI Dashboard AD'[Start Date])), CALCULATE(Sum('Date'[Business Day Value]),
FILTER('Date', 'Date'[DateColumn] <= 'KPI Dashboard AD'[Campaign Creation Date] &&
'Date'[DateColumn] > 'KPI Dashboard AD'[Start Date])) * -1 )

 

 

This is a little out of my depth, as I am still a newbie.

 

I think the only hang up is the valye that should be in Date[Date Column] - not sure what this should be?

 

Capture.PNG

 

This is my statement for my date table...

 

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthShortYear",FORMAT([Date], "mmm-YYYY"),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"Week Number", WEEKNUM ( [Date] ),
"Week Number and Year", "W" & WEEKNUM ( [Date] ) & " " & YEAR ( [Date] ),
"WeekYearNumber", YEAR ( [Date] ) & 100 + WEEKNUM ( [Date] )
)

 

Thanks again!

I used DateColumn since I could not know what name you had chosen for your date column in your calendar. So, replace 'Date'[DateColumn] with 'Date'[Date].

@erik_tarnvik

 

Thanks for the continued help...This is the most recent result.

 

Int neither works a s a Measure or Column, not sure whoch it should be.

 

Any suggestions?

 

Capture.PNG

I intended it as a measure.

 

The culprit is this statement:

 

Business Day Value = if('Date'[DayOfWeek]="Monday"||
'Date'[DayOfWeek]="Tuesday"||
'Date'[DayOfWeek]="Wednesday"||
'Date'[DayOfWeek]="Thursday"||
'Date'[DayOfWeek]="Friday",
"1",
"0")

This statement results in a string of either "1" or "0". Change this to:

Business Day Value = if('Date'[DayOfWeek]="Monday"||
                        'Date'[DayOfWeek]="Tuesday"||
                        'Date'[DayOfWeek]="Wednesday"||
                        'Date'[DayOfWeek]="Thursday"||
                        'Date'[DayOfWeek]="Friday",
                        1,
                        0)

and make sure that the Business Day Value column in your calendar table is of type Whole Number. That should do it.

 

I should mention also that you could use DATESBETWEEN function with your calendar table for a more elegant solution but I figured my example would be easier to follow. See

 

https://msdn.microsoft.com/en-us/library/ee634557.aspx

v-jiascu-msft
Employee
Employee

Hi @irnm8dn,

 

It seems you use it as a calculated column. Maybe you can try it like this:

 

Day Count of Drop =
[Start Date] - [Campaign Creation Date]

Day Count Business Days Between Two Dates.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft

 

Dale, thanks for the input.  Two questions:

 

1.  There are some instances where my Start Date is before my Creation Date.  Although this is an error, I am not sure your proposed solution will work.  Thoughts?

 

2.  How does your solution solve for my business day need?

 

Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.