Average of first hour of each day

[EasyNet]

Regards !

I would be very grateful to someone who can help me with this request. I have a table that contains the initial time and the final hour in which a vendor made a sales visit. I must calculate the average in which that seller makes his first and last visits in the day, according to the days he selects in the filter. I tried using functions like TIMEVALUE - AVERAGE - CALCULATE - FILTER - ETC. but none works for me. In SQL i can convert the hour to minutes but in DAX do not have this function. The Hour is in text format. And I am in Direct Qyery mode.

Thank you very much !

[Greg_Deckler]

Could you post some sample data?

Should be able to use VALUE to get the numeric equivalent of your text value. But I'm not clear if your Hour is a number of hours or an actual Hour, 01:00:00 AM, for example.

[EasyNet]

Thank you very much for your help. the format is text and hava a real hour like to your example. in this picture you will can see it, i tried with this dax formulation in calculate. AVERAGE FIRST HOUR = CALCULATE(AVERAGE(VW_BIG_EASY_MAPS[HORA INICIO VALOR]),FIRSTDATE(VW_BIG_EASY_MAPS[HORA INICIO VALOR])). but is wrong.

Thanks

[Greg_Deckler]

Take a look at the techniques in this artcle. I feel that you will need to essentially extract just the numeric hour from your current hour column and then base your calculations on that.

http://community.powerbi.com/t5/Desktop/Aggregating-Duration-Time/m-p/13350/highlight/true#M3358

[EasyNet]

Thank you very much for the contribution, it has helped me a lot to be able to solve the query, and I managed to obtain the total number of seconds that it has every hour with the following formula as a column.

AV TOTAL SEGUNDOS = (SECOND(VW_BIG_EASY_MAPS[HORA INICIO VALOR])) + (MINUTE(VW_BIG_EASY_MAPS[HORA INICIO VALOR]) * 60) + (HOUR(VW_BIG_EASY_MAPS[HORA INICIO VALOR]) * 3600)

since I already have the time as a value, I can calculate the average of different hours thus represented, but now what I need is that I calculate that average only for the first hours of each day, that is to say that the formula identifies which is the first hour of each day and you can obtain your averages, so that by applying a filter as a range of dates when selecting a week you can see the average time in which that seller made his first visit in that week. already with that data I look for the way to convert those second averages in an hour, which is the data that I need to calculate.

Thank you very much for your help.