DAX formula to create a New Measure (number of hours between starting date time and end date time)

[Mega79]

Hi all!

I need to create a New Measure DAX formula to sum up the number of hours between "starting date time" and "end date time" of two columns. The formula needs to exclude the end date time cells that do not have any value.

What I really want is to get the average number of total hours between dates for a given month.

e.g.  15 Hours for the month of february.

Can someone help me with this?

Thanks in advance.

[wylee1]

Hello,

I am new to Power BI and facing an similiar issue but could not fix it. Can someone advice me?

DateTimeStart     DateTimeEnd        ToolName              Operation      User          sumhours
31-Oct-16 9:00      31-Oct-16 17:00    HiSeq 2000 Engg     TrainServ         Bernard     error

I try to add new column call "sumhours"  > sumHours = sumx(timeTable, DATEDIFF(timeTable[DateTimeStart], timeTable[DateTimeEnd], SECOND))/3600  but have error..  is "timeTable" refering to the table name?,?

Appreciate your time.. advance thanks

[AlexChen · Accepted solution]

Hi,

I updated the data in your table to try to give an example. The data of February is from yours.

1. Firstly you have to create 2 new columns from “end date time” column. One is “Month”, the other is “Date”.

See screenshot below. You can create them from “query editor” -> “add column”.

2. create measure “sumHours”:

sumHours = sumx(timeTable, DATEDIFF(timeTable[starting date time], timeTable[end date time], SECOND))/3600

3. create measure “countDaysByMonth”:

countDaysByMonth = CALCULATE(DISTINCTCOUNT(timeTable[date]), ALLEXCEPT(timeTable,timeTable[Month]))

4. create measure “monthlySumHours”

monthlySumHours = CALCULATE([sumHours], ALLEXCEPT(timeTable, timeTable[Month]))

5. create measure “monthly average hour”:

monthly average hours = [monthlySumHours]/[countDaysByMonth]

6. Now create a table visual and move measures above to it. You will see that measure “monly average hours” is what you want.

[Mega79]

Thanks a lot for your response. It helped!

[RutchaNgam]

I need top create average each. rows (I s'nt sum average), I don't konw. How to use function in Power BI? 

[samdthompson]

1. Make a date table

2. Create average, min, max, sum etc measures based on start and end eg:

=sumx(table,table[end_time]-table[start_time])

OR

create calculated column [end_time] - [start_time]

create measure:

=
IF (
    HASONEVALUE ( table[difference] ),
    CALCULATE ( AVERAGE ( table[difference] ), BLANK () )
)

3. make chart with month from date table on axis, measure from table as values.

[RutchaNgam]

I want to know, How I can  create a New Measure DAX formula to sum up the number of Days between "starting date time" and "end date time" of two columns. 

[samdthompson]

@RutchaNgam days elapsed=max(table[date])-min(table[date])

[RutchaNgam]

Thank you So much!