topic Re: Using DAX to calculate the total free hours between dates in DAX Commands and Tips

Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 09:49:25 GMT

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

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 10:52:48 GMT

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

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 12:00:23 GMT

I'm experiencing an issue where It doesn't want to recognise the column names with the code it seems to find the table quite alright but not the columns I made sure that they are are typed correct and everything.

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 12:51:25 GMT

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

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 12:53:23 GMT

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

Measure 2: hoursperday = SUM('table'[hoursperday]

Measure 3: [days off] * hoursperday

Could potentially be a solution, but still send your code so i can have a look 🙂

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 12:56:29 GMT

Here it is! also the error states it cannot get a single value I did try to look into this but I dont need all the rows to be summed together as they all have different values that should be displayed. I wasn't sure of how to do that however I hope this helps you understand the issue 🙂 thanks in advance!

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 13:44:31 GMT

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.

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 13:49:39 GMT

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.

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 13:51:20 GMT

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

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 13:52:07 GMT

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

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 13:56:28 GMT

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!

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 14:05:48 GMT

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

Re: Using DAX to calculate the total free hours between dates

Anonymous — Fri, 21 Apr 2023 08:19:49 GMT

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

Re: Using DAX to calculate the total free hours between dates

Anonymous — Thu, 20 Apr 2023 14:48:06 GMT

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

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

Re: Using DAX to calculate the total free hours between dates

Anonymous — Fri, 21 Apr 2023 06:34:23 GMT

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

Re: Using DAX to calculate the total free hours between dates

Anonymous — Fri, 21 Apr 2023 07:33:01 GMT

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

Re: Using DAX to calculate the total free hours between dates

Anonymous — Fri, 21 Apr 2023 07:45:47 GMT

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?

Re: Using DAX to calculate the total free hours between dates

Anonymous — Fri, 21 Apr 2023 07:48:22 GMT

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?

Re: Using DAX to calculate the total free hours between dates

Anonymous — Fri, 21 Apr 2023 08:05:10 GMT

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

Re: Using DAX to calculate the total free hours between dates

Anonymous — Fri, 21 Apr 2023 08:07:35 GMT

Yeah there's a bracket after DAY

so the end of your code looks like this:

DAY(
))

it should look like this:

DAY

)