Cumulative Total Per Hour

[Syndicate_Admin]

Hello again

Sorry for the inconvenience, I have another question, I have a total of consultations in a period of one month, let's say 1000, however, at the end of that time, I need to know how many consultations were closed at 8 in the morning?, how many at 9 am., plus those at 8 am?, How many at 10 am, considering the one at 9am and the one at 8am? and so on every hour until 8 p.m. and represent them on a bar chart.

Does anyone have any suggestions?

In advance I thank you for your attention

Best regards

In advance thank you very much

Best regards

[v-xiaotang]

Hi @Syndicate_Admin @Adee_7 

You can create a measure to filter the table in a time period and then count the rows,

e.g.

measure= 

  var _timeStart= use function to get your time start

  var _timeEnd= use function to get your time end

return

  calculate(countrow('table'),filter(all('table'),'table'[time]<= _timeEnd && 'table'[time]>= _timeStart))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Good afternoon, I am trying to implement the solution they are providing me but I do not understand very well this part, where they tell me:

var _timeStart= use the function to get your time started

var _timeEnd= use the function to get the end of your time

Could you explain to me what functions are you referring to? o How can I create them? I as I tell you I have a catalog of the hours between 8 in the morning and 20:00 hrs but I do not understand very well How can I call it through a function? I hope you can support me with that. In advance I thank you infinitely

Best regards

[v-xiaotang]

Hi @Syndicate_Admin  @Adee_7 

The function needs to be customized according to your model.

Usually we need information below, could you provide it? Thanks

 (1) a sample file, you can replace raw data with bogus data to protect your privacy.

     or provide some sample data that fully covers your issue/question.

(2) give your expected result based on the sample you provide.

Kindly note: Please ensure the data in sample is concise and representative.

Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hello!!

I generated the file, but when I try to upload it it sends me this error:

Can you tell me how can I share them?

The other way I came up with is by taking screenshots...

These are my data:

My lookup table

My Catalog of hours

How to show my cumulative closing agreement with the range of hours (this I generated manually, for the example)

As you can see my graph (All the hours mark me 13, because it is the total of queries I have, but it does not report the cumulative sum of closures per day)

As you should see... Increasing from 0 which is how you start the account to 13 which is the total of my queries

Thank you very much for everything, I am attentive to your comments

Best regards

[amitchandak]

@Syndicate_Admin , Create a date time in your table with hour only

date hour = datevalue([Datetime]) +time( hour([Datetime]),0,0)

then a measure like

calculate(sum(Table[value]), filter(allselected(Table[Datehour]), Table[datehour]<= max(Table[Datehour]) ) )

[Syndicate_Admin]

Refers to Create a table,

You will create a table like this.

Then you have to format the Column (hours).

After that you will be ready to create the measurement.

AcumHoras =

WHERE CALCULATE =

CALCULATE(

COUNTROWS(

TablaX

)

,FILTER(ALL(TablaX)

,TablaX[Time] <= .MAX(TablaHoras[Hours])

)

)

RETURN CALCULATE

Slds.

[Syndicate_Admin]

Thank you for answering me @Syndicate_Admin, I have been trying to implement it, but there is something that I do not understand very well, you tell me that I have to create a table with the date and from there extract the time, I in the information I have, I have a column where the closing hours are. At the time of applying the code I would have something similar to this:

Acumulado = CALCULATE(SUM('consultas consultas[statuscerrados]), FILTER(ALL('consultas consultas'),'consultas consultas'[hora_cierre] <= max ('consultas consultas'[hora_cierre])))

If I put it in my graph everything gives me 1 😥 and well that is not the correct result, for example between 9 and 10, there should be 647

In advance thank you very much

[Syndicate_Admin]

Hello, sorry for the inconvenience, will anyone have any news with the latest I said?

In advance thank you very much