Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DateTrunc

Hello, 

I have the following report query from Solarwinds and would like to replicate it within Power BI.

 

SELECT Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Nodes.DetailsUrl AS NDetailsUrl,
Nodes.VendorIcon AS Vendor,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
Interfaces.DetailsUrl AS IDetailsUrl,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95 FROM Orion.Nodes INNER JOIN Orion.NPM.Interfaces ON Nodes.NodeID = Interfaces.NodeID INNER JOIN ( SELECT OuterInterfaceTraffic.InterfaceID,

(SELECT MAX(InAveragebps) as maxInMaxbps FROM ( SELECT TOP 95 PERCENT InAveragebps FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE()) ORDER BY InMaxbps ASC )) AS Maxbps_In95,
(SELECT MAX(OutAveragebps) as maxOutMaxbps FROM (SELECT TOP 95 PERCENT OutAveragebps FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE()) ORDER BY OutMaxbps ASC )) AS Maxbps_Out95,
(SELECT MAX(Maxbps) as maxMaxbps FROM ( SELECT TOP 95 PERCENT Maxbps FROM (SELECT (CASE WHEN OutAveragebps > InAveragebps THEN OutAveragebps ELSE InAveragebps END) AS Maxbps FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())) AS MaxbpsSet ORDER BY Maxbps ASC )) AS Maxbps_95 FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic WHERE OuterInterfaceTraffic.DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE()) GROUP BY OuterInterfaceTraffic.InterfaceID ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID

 

The problem is that DateTrunc is not a recognized built-in function name. Can anyone please help me customize this query to work within Power BI? 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I am trying to pull 95th percentile for the previous month. 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

I am trying to pull 95th percentile for the previous month. 

Hi @Anonymous ,

It seems that you want to convert the query to dax in power bi.

You could refer to this article at first.

In addition, you could try MONTH () to replace DateTrunc('month', GETUTCDATE()).

If you still need help, you'd better share some data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors