topic Re: peaks by hour and reporting with to reference dates in DAX Commands and Tips

peaks by hour and reporting with to reference dates

Anonymous — Fri, 02 Oct 2020 13:39:27 GMT

Hi,

I'm new in the community so to present myself I'm Benjamin, I work in a company which repair datacenters problems 🙂

Despite my 8 hours traning on power BI, there're several things I don't manage to do. And after several hours of research I prefer to ask.

I've a database of incidents (cases) opened and closed in the datacenters.

To simplify, I mostly need 3 columns :
- incident_ref = text --> that's the reference of each opened cases
- open_time = dd/mm/yyyy hh:mm:ss for each cases
- closed_time = dd/mm/yyyy hh:mm:ss for each cases

I need to calculate two indicators :

PEAK >200 -- > I need to calculate, on an hour basis, when there's more than 200 incidents. In Excel, I make a dynamic cross table (nb of rows by hours) and apply a sum.if > 200 on it, and I display the result for the month. I have to use the column "open_time" for this. For example : if hour 1 = 196 ; hour 2 = 206 ; hour 3 = 120 ; hour 4 = 400, then the total to calculate is 400+206 = 606 cases in peak.

Nb of cases --> I also need to calculate the total amount of cases for this month. Easy. But this measure has to be done with the closed_time (for the billing).

The aim is to build a reporting board which display :

Month Total cases PEAKS >200 Total cases - PEAK>200
August 4000 400 4000-400 = 3600

I don't manage to correctly build a board with two measures based on two different date time. And I also don't manage to calculate the peak in a measure. The fact that the date are with precise hh:mm:ss is also a problem to calculate peak by hour.

I really thank you for your help, and I'm sorry for my bad english.

How would you do this ?

Benjamin

Re: peaks by hour and reporting with to reference dates

daxer-almighty — Fri, 02 Oct 2020 19:37:24 GMT

This is not clear. Please post some examples, maybe screenshots... maybe some data. Generally, everything that clarifies the issue is good.

Re: peaks by hour and reporting with to reference dates

stevedep — Sat, 03 Oct 2020 11:46:52 GMT

Hi,

This should do the trick:

_CasesInPeak = SUMX( SUMMARIZE(tblPeak,[_Month],[_Day],[_Hour], "c", COUNTROWS(tblPeak)) , IF([c] > 200,[c],BLANK()) ) _Month = MONTH('tblPeak'[open_time]) _Day = DAY('tblPeak'[open_time]) _Hour = HOUR('tblPeak'[open_time]) _TotalCasesMinusCasesInPeak = COUNTROWS(tblPeak) - [_CasesInPeak]

As seen here (days as rows, hours in columns):

Testdata generated with:

tblPeak = var _tbl = SELECTCOLUMNS( CROSSJOIN( ROW("test", DATE(2020, 10,3)), GENERATESERIES(1,28,1) ), "StartDate", [test], "Increment", [Value]) var _dates = ADDCOLUMNS( _tbl, "NewDate", [StartDate]+[Increment]) return SELECTCOLUMNS( ADDCOLUMNS( CROSSJOIN( _dates, GENERATESERIES(1,1100,1)), "open_time", [NewDate] + NORM.INV(RAND(), 0.5 + ([Increment]/100), 0.08), "incident_ref", CONVERT( UNICHAR(RANDBETWEEN(65,90)),STRING) & CONVERT( UNICHAR(RANDBETWEEN(65,90)),STRING) & CONVERT( UNICHAR(RANDBETWEEN(65,90)),STRING) ), "open_time", [open_time], "incident_ref", [incident_ref] )

Link to file.

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards,
Steve.

Re: peaks by hour and reporting with to reference dates

Anonymous — Sun, 04 Oct 2020 12:27:48 GMT

hi,

Thank you very much for your answers and I'm sorry if it's not really clear.

I will try the solution at work tomorrow and keep you up to date.

Benjamin

Re: peaks by hour and reporting with to reference dates

stevedep — Sun, 04 Oct 2020 12:45:15 GMT

Hi,

Welcome. Will await your response tomorrow. Pls tag me in your response.

Have a nice Sunday. Kind regards, Steve

Re: peaks by hour and reporting with to reference dates

Anonymous — Mon, 05 Oct 2020 09:46:23 GMT

Many thanks !

It works perfectly, and the dashboard is wonderfull.

Thanks again for your reactivity and help

Benjamin

Re: peaks by hour and reporting with to reference dates

stevedep — Mon, 05 Oct 2020 11:06:55 GMT

Thanks 🙂

Glad to be of help!