Take the last 5 minutes from the max time

[Anonymous]

Hi,

I have a table of calls updated from production for every ETL DB refresh.

I need to show stats about calls based on the following :

Showing stats for the last 5 minutes until the max time of call inserted : to be more clear i should find the last call inserted and from it's call time I should calculste number of calls from 5 minutes to this call.

Any help?

[isabella]

Hi @Anonymous 

You want to import the last 5 minites data into power bi desktop,right? If your database is from SQL Server ,you can use below query statement to import the data.

My test table is as this :

Use the query statement to get the last 5 minites data:

  DECLARE @MAXDATE DATETIME2 = (SELECT MAX(DATEINSERTED) FROM tablecall)

  DECLARE @LEFTDATE DATETIME2 = (select DATEADD(mi,-5,@MAXDATE))

  SELECT * FROM tablecall  where DATEINSERTED > @LEFTDATE ORDER BY DATEINSERTED

The imported data  is as this:

Best Regards,

Community Support Team _Isabella

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

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

measure  =

var _max = maxx(allselected(Table), Table[Last Call Datetime])

return

calculate(Count(Table[Call ID]), filter(Table, Table[Last Call Datetime] > _max -time(0,5,0) && Table[Last Call Datetime]  <= _max) )