Using DAX to calculate the total free hours between dates

[Anonymous]

Good day to anyone who reads this,

I am a student taking my first steps with dax and power BI for a Data analyst internship and i've ran into a little problem I'm currently trying to figure out how to use Dax to calculate the amount of time someone got off work. These hours are calculated from the days between a start date and end date with another collumn showing how many hours per day they had off work(see excel recreation below) 

I've been looking around how to get this to work with DAX and I've tried to use the following code as a first step to count the amount of days between the two dates but got no results.

Days =

DATEDIFF(
	SELECTEDVALUE( TT_EMP_BOOK[TT_FROMDATE]),
	SELECTEDVALUE( TT_EMP_BOOK[TT_TODATE]),
	Day(
)

I've also tried looking on the DAX pages provided by microsoft temselves to see what syntax I might be putting down wrong but can't seem to figure it out its all still quite new to me. 

In the end what I hope to achieve is calculating the following and putting it in a new column
Days between 01/01/2023 - 03/01/2023 = 3 * hours per day 8 = 24 hours in total 

hopefully someone can lend me a hand and if so thank you in advance.

Edit note: I am using DAX studio 3.0.7 to build my DAX code before using it

[Anonymous]

so it's saying that because we've referenced a column and not specified which row, so power bi is like "okay you want a date from this column, but which one?". Normally we can specify we want the MIN date by MIN('table'[Date]) or MAX('table'[date]) but in this case i'd say it might be possible to get the result you want by wrapping the table names and column names in MIN() or MAX(). 

If this doesn't work there's a more proper way we can try but give this a go.

[Anonymous]

I did put the MIN infront of them and even tried adding one of both but another error returns no matter what variation I put in hopefully its not a mistake I made by missunderstanding. I hope this is what you meant but so far it isn't working just yet.

[Anonymous]

DAY() shouldn't have brackets in third argument. just put DAY.

[Anonymous]

You just need to add ending brackets for the min functions 🙂 Have a look, if you can't figure it out paste the code here so i can write it for you

[Anonymous]

Mabey im just being stupid or well lets say inexperienced 🙂 but i they have they respective brackets already I think.

I'll still paste the code down here since I'm probably just not aware of how to do it properly

Total Hours = 
    VAR _hoursPerDay = MIN('TT_EMP_BOOK'[TT_ENDTIME])
    VAR _numberOfDays = DATEDIFF(MIN('TT_EMP_BOOK'[TT_FROMDATE]), MAX('TT_EMP_BOOK'[TT_TODATE], DAY))

Return
    _hoursPerDay * _numberOfDays

Also just wanna say thank you for taking the time to help and explain things really appreciated so far!

[Anonymous]

Sorry, add a closing bracket to the MAX( function i mean!

[Anonymous]

Okay the formula definitely is not giving errors anymore however its giving the same value for every row in the table instead of each row their own calculated value. im not entirely sure why but some days its only 1 hour of dental leave yet it gives 1067 here

[Anonymous · Accepted solution]

This is because we are using the min value from the whole table, so this hasn't worked. Apologies. Here's a revised idea to get this to work:

daysOff =

DATEDIFF(
	 TT_EMP_BOOK[TT_FROMDATE],
	 TT_EMP_BOOK[TT_TODATE],
	Day
)

This is your days between calculated column.

1. Create new column again

2. Enter this code in the column:
answer = ProductX('table', daysOff * hrsperday)

let me know, sorry for all the confusion, i think i overcomplicated this.

Edit: to correct code

[Anonymous]

Also perhaps I'm wrong but I think the DAY statement here would return what day number of the month it is instead of the amount of days between the start and end date although I could be wrong?

[Anonymous]

DAY() returns the day number from a date
DAY is a parameter option in the DATEDIFF function 🙂 So yes you're correct. Did you try the solution?

[Anonymous]

Yes I did you can find it in msg 14! 🙂 It didn't wanna work unless I'm doing something wrong here.

[Anonymous]

Also, just as a quick note:

When you're posting in the forums, instead of taking screenshots of code, always copy and paste it in. You can include a screenshot too if you want to, but if you paste the actual code then we can fix it for you, and repost it back so you can just copy and paste it in. Less room for bracket errors and things like that and let's me take a look at the code in dax studio 🙂 

Just a helpful tip! 

[Anonymous]

Appreciate the tip very new to forums like this so it all helps!

[Anonymous]

Yeah there's a bracket after DAY

so the end of your code looks like this:

DAY(
))

it should look like this:

DAY

)

[Anonymous]

Yes It worked now using the code from msg 12 and 13 just needed to remove that bracket after DAY thank you so much I'm marking its as the solution I really appreciate your efforts and time! 🙂

[Anonymous]

No problem at all pal! Really glad we could get to a solution 🙂

Have a great day and welcome to the PBI community! It gets a lot easier quickly, i promise! 

[Anonymous]

Is there an issue with simply doing this in a calculated column:

test = [daysOff] * 'table'[hrsperday]

[Anonymous]

I did try this before and for some reason it will not accept DAY as a vaid argument eventhough it is one? the second solution you posted containing test = [daysOff] * 'table'[hrsperday] could work however the issue is that there is no days off column yet.

Edit: This is what the column shows by the way

[Anonymous]

No problem! 🙂 could you send a screenshot of the error message and your dax code?
Cheers!

[Anonymous]

Total Hours = 
    VAR _hoursPerDay = 'table'[hoursperday]
    VAR _numberOfDays = DATEDIFF('table'[start date], 'table'[end date], DAY)

Return
    _hoursPerDay * _numberOfDays

Give this a try.

Another potential solution:
Measure 1: Days off = DATEDIFF('table'[start date], 'table'[end date], DAY)

Measure 2: 'table'[days off] * hoursperday

then put these into your table. Let me know if you eed anything else or if this works. If it does please accept as solution