peaks by hour and reporting with to reference dates

[Anonymous]

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

[stevedep · Accepted solution]

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. 

[Anonymous]

Many thanks !

It works perfectly, and the dashboard is wonderfull. 

Thanks again for your reactivity and help

Benjamin

[stevedep]

Thanks 🙂

Glad to be of help!

[Anonymous]

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

[stevedep]

Hi,

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

Have a nice Sunday. Kind regards, Steve

[daxer-almighty]

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