Calculate # of Days - Based on Month and if EndDate ends during the month

[LyonsBI_BRL]

Currently working on my companies finance PowerBI solution though I'm working to convert what was being done in Excel to PowerBI.

Scenario: I'm able to calculate # of Working Days excluding Weekends and Holidays no problem which is what I see in the "Working Days column". To the right I have the total number of Billable hours for November 2021 and total number of Billable Days for November 2021. 

What I'm trying to find here is what would be the user's Total number of Billable Days and Billable Hours based off their End date in November. As some end on November 9th, November 12th and November 16th. 

Basically it's a filter of saying "Based off this employee's StartDate and EndDate, only calculate the total number of billable days and hours to their end date

Any help would be much appreciated. 

Thanks!

[TheoC]

Hi @LyonsBI_BRL,

Days Between = 

VAR _MonthStart = DATE ( 2021, 11, 01 )
VAR _MonthEnd = EOMONTH ( _MonthStart , 0 )
VAR _dateBegin = 'Table'[dateBegin]
VAR _dateEnd = 'Table'[dateEnd]
VAR _StartDate = IF ( _dateBegin >= _MonthStart , _dateBegin , _MonthStart )
VAR _EndDate = IF ( _dateEnd <= _MonthEnd , _dateEnd , _MonthEnd )

RETURN 

IF ( MONTH ( _EndDate ) <> MONTH ( _MonthStart ) , 0 , INT ( _EndDate - _StartDate ) )

The outputs are below.  In terms of the Billable Hours, I just did Days * 7.5 so please ensure to adjust to your columns.  Hopefully the variables make sense. I find them to be far simpler than when they're not used so hopefully they help you 🙂 

I hope this helps! 🙂

[LyonsBI_BRL]

@TheoC 

Wow just wow!!! Okay that's a lot closer than I was three days ago! Is there anyway to modify it so that it excludes the weekends and Holidays from a Holiday table?  Thanks!

[TheoC]

@LyonsBI_BRL, haha champion! Yes, definitely. Where are you based? What are the public holidays you want to be filtered? 

[LyonsBI_BRL]

@TheoC 

It's these holidays listed below would be the ones that need to be observed and filtered out of the calculation.

[TheoC · Accepted solution]

@LyonsBI_BRL 

Alright, finally.... apparently a little more challenging than "<>" LOL!

I have updated the formula. However, you will also need to add a Dates table & Public Holidays table.  

Dates table is as follows and no relationship is needed to other tables.

Once you have the Dates table, add the following Calculated Column for the Weekday:

Then you can add the Public Holidays table (make sure you convert your dates to Date). I simply used the "Enter Data" function and pressed Ctrl C and Ctrl V (copy / paste) shortcuts, then converted Date Type to "Date".

Once this is in, you can create a Calculated Column in your Date table using LOOKUPVALUE like below:

And finally, you can then update the below! 🙂  

Enjoy mate! And make sure to give me a high five and tick as solved LOL!!!! 😄

[VaishnaviGandhi]

Hi @TheoC 

I have followed the DAX formula here however the issue in my case is if the employee has a start date of 01/01/2024 and the ned date is of 31/12/2024 the no of working days are only being counted in the month of december. I want to count the total no. of days per employe per month for the year of 2024. 

Also is there a way to deduct holidat entitlement which is 20 days a year (this is excluding * public holidays which is alredat present in my base logice just need to find a way to deduc 20 leave per year this is only if the eploye has worked the whole year if they have worked ifr 6 months then that should be 10days and so on. 

Desierd Outcome

Month	employee	Start date	Enddate	No of working days
Jan	x	01-01-2024	28-01-2024	22
Jan	y	02-01-2024	31-12-2024	22
Jan	z	02-02-2024	04-04-2024	0
Feb	x	01-01-2024	28-01-2024	0
feb	y	02-01-2024	31-12-2024	19
Feb	z	02-02-2024	04-04-2024	18
March	x	01-01-2024	28-01-2024	0
March	y	02-01-2024	31-12-2024	22
March	z	02-02-2024	04-04-2024	22
April	x	01-01-2024	28-01-2024	0
April	y	02-01-2024	31-12-2024	22
April	z	02-02-2024	04-04-2024	2

Thanks

Vaish

[TheoC]

Hi @VaishnaviGandhi 

If you want to apply the DAX formula I have created in this post, you need to create your data to be in the same structure.  In your data, you do not need to write down each month and each employee on different rows.  Just have a Start Date column and an End Date column in a single row for a single employee.  Below is an exampe of the structure you should have. When you do this, you can then apply the DAX formula I've put together.  You will also need to create a Date table and a Public Holiday Date table as I have done in the seperate comment on this post.  All of this information you need is in this post but most importantly simplify the structure of your data to match the below:

Employee Id	Start Date	End Date
X	1/01/2023	31/12/2023
Y	3/03/2023	25/12/2023
Z	8/08/2023	11/12/2023

All the best.

Theo

[VaishnaviGandhi]

Hi @TheoC 

Thank you for your quick response I have followed everything as per the post however if you see the below ss instead of the working days distributed throughout all the months an employee is working it only appears in the December month as per the start and the end date. 

the outcome i.e., no. of working days; should spread out through all the months Mr. X is employed. thsi is required to show the revenue forecast per month for contract base employees, which is why it would be helpfull if it was be ditributed in months. 

Please help if you can, the distrubution is to be across the months 
jan - 22 days, feb -20 days still the date the employee is working. 

Looking forward to hearing form you.

Regards
Vaish

[TheoC]

@VaishnaviGandhi unfortunately, that is a different requirement to what is in this post. I suggest posting a new thread and adding example data. From here, I or another Community member can jump on to assist with a solution.

[LyonsBI_BRL]

HELL YES! IT WORKED! @TheoC thank you so much! I can't tell you how much I've been going back and forth trying to solve this and the number of YouTube videos I've been watching trying to figure this one out. Thank you so much this is awesome! 

[TheoC]

@LyonsBI_BRL it is a pleasure big fella! All part of why this amazing Power BI Community exists!

All the best with your Power BI journey mate!

[TheoC]

I got you mate. I will update formula as soon as I get in front of the computer.  On phone atm but it's a very quick update. All we do is create a VAR _HolidaysDates referencing the holidays and use <> to exclude. Give me an hour k! 🙂

[LyonsBI_BRL]

@TheoC Hey Theo!

Got a question, instead of having this be a calculated column, would there be any chance this could be a measure instead? So that I could have a measure I could attach to each month. Otherwise I'd have to do this for 12 months out, plus a bunch of other column calculations for this as well. 

I've tried using the same formula here, but am not getting anywhere. 

Thanks!

[TheoC]

Hi @LyonsBI_BRL 

Thanks for reaching out.  I'm not sure I am understanding the issue that the Calculated Column is causing regarding months etc. However, in saying that, I haven't tried (and am on phone), you should be able to copy and paste the formula as a new measure. 

Give it a go and let me know if it achieves the outcome you're wanting and if not I can help you get to where you need 😀

[LyonsBI_BRL]

@TheoC Think I figured it out. 

So first I needed to know the number of workable days each person is working on multiplied by 8 gives me their total number of hours. Giving my _monthStart Date being 01/01/2021 as to how many days / hours they have worked or will be working for the year.

Forecast Workable Hours = VAR _MonthStart = DATE ( 2021, 01, 01 )
VAR _MonthEnd = EOMONTH ( _MonthStart , 12 )
VAR _dateBegin = [dateBegin]
VAR _dateEnd = [dateEnd]
VAR _StartDate = IF ( _dateBegin >= _MonthStart , _dateBegin , _MonthStart )
VAR _EndDate = IF ( _dateEnd <= _MonthEnd , _dateEnd , _MonthEnd )
VAR _NumberWorkDays = CALCULATE( COUNTROWS('Date'), DATESBETWEEN('Date'[Date], _StartDate, _EndDate), 'Date'[Weekday] = TRUE || 'Date'[Public Holidays] <> 0)

RETURN 

IF ( ISBLANK (_NumberWorkDays), 0, _NumberWorkDays) * 8

What I need to do here break this out by Month to month within a measure instead of a column. 

When I attempt to break this out into a measure, it doesn't know what [dateBegin] or [dateEnd] is so I'm assuming I'm missing some key piece here. 

See this calculation is what leads into calculating the other fields here for Finance 

Thank you again @TheoC please let me know what I need to change here. 

Thanks! 

[TheoC]

Hi @LyonsBI_BRL 

I've always thought measures were recognisable. I'm unsure why they're not in your instance.  I recommend just copying and pasting whatever the [dateBegin] and [dateEnd] measures are that you have created into the VAR _dateBegin and VAR _dateEnd space.  Basically, rather than using a separate measure, use the measure formula in the VAR slots and do not worry about using the separate measures.

Let me know if that makes sense?

Theo 

[LyonsBI_BRL]

@TheoC 

Okay one step closer to getting this working like I need it to. I created two measures for getting the dateBegin and dateEnd date for each person. Made the beginning of the year 

DateEnd2 = VAR EndDate = CALCULATE(MIN([dateEnd]))
RETURN
    EndDate

DateBegin2 = 
VAR BeginDate = CALCULATE(MIN([dateBegin]))
RETURN
    BeginDate

Then modified the measure to include the datebegin and dateEnd

Forcast Number of Hours = 

VAR _MonthStart = DATE ( 2021, 01, 01 )
VAR _MonthEnd = EOMONTH ( _MonthStart , 12 )
VAR _dateBegin = [DateBegin2]
VAR _dateEnd = [DateEnd2]
VAR _StartDate = IF ( _dateBegin >= _MonthStart , _dateBegin , _MonthStart )
VAR _EndDate = IF ( _dateEnd <= _MonthEnd , _dateEnd , _MonthEnd )
VAR _NumberWorkDays = CALCULATE( COUNTROWS('Date'), DATESBETWEEN('Date'[Date], _StartDate, _EndDate), 'Date'[Weekday] = TRUE || 'Date'[Public Holidays] <> 0)

RETURN 

IF ( ISBLANK (_NumberWorkDays), 0, _NumberWorkDays) * 8

This in turn gave me the correct number of hours for each person through the measure

The weird part is when I try to break it down by each month it returns to this

Ideally it should be if I filter on October, November and December from the measure it should show me the number of possible working hours for each of those months. 

Any suggestions?

[TheoC]

Hi @LyonsBI_BRL 

In all honesty, I'm not sure what is going on but I think it's to do with this:

It is a bit hard without seeing the actual file.  Would you be okay if we had a Teams catch up to go over the item?  If so, send me a Private Message and we can go from there?

[LyonsBI_BRL]

@TheoC Check your inbox just sent it over