cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manojsv16 Regular Visitor
Regular Visitor

Bar chart for displaying last 24 hours Meter Outages

Hello Team,

 

I have a requirement to create a bar chart to calculate the count of meter outages for last 24 hours. I have a table with one fields as date (Timestamp format) and other fields as meter outage ID.  I need to pull the latest date and need to show the outages in last 24 hours.

 

For example: if i have latest date as "12/13/2018 12:45:30", then i need to create a bar chart using the following. I would like to know how we can get the latest date and travel back to 24 hours as shown below. Also want to calcualate the count of outages for each hours (Last 24 hours) from the latest date.

 

I would appreciate any suggestions on acheving the this scenario. Thank you!!

 

Date (X- Axis)Count of Outages
12/13/2018  12:00:0010
12/13/2018  11:00:004
12/13/2018  10:00:0015
12/13/2018  09:00:0050
12/13/2018  08:00:0020
12/13/2018  07:00:0010
12/13/2018  06:00:008
12/13/2018  05:00:0034
12/13/2018  04:00:0056
12/13/2018  03:00:0024
12/13/2018  02:00:0082
12/13/2018  01:00:0015
12/13/2018  12:00:0010
12/12/2018  23:00:0034
12/12/2018  22:00:0015
12/12/2018  21:00:0010
12/12/2018  20:00:004
12/12/2018  19:00:0032
12/12/2018  18:00:0022
12/12/2018  17:00:0082
12/12/2018  16:00:0024
12/12/2018  17:00:0021
12/12/2018  16:00:0012
12/12/2018  15:00:0014
1 REPLY 1
CiceroBC Regular Visitor
Regular Visitor

Re: Bar chart for displaying last 24 hours Meter Outages

Hi @manojsv16,

 

I think I have a solution to your issue. Using only a simple list of DateTimes (I've included a list of dummy values in the M below), I put in custom columns and then added a DAX function to find if any entry was within the last 24 hours of the MAX value. Here is a rundown of activities in Power Query (M), DAX, and the filter used - with a screenshot of the final solution.

 

Activities done in M (transform data):

-Add custom column to truncate minutes and seconds from the DateTime in the dataset

-(not used here, but since you mentioned meters, which frequently use "Hour Ending") Add an hour if it is past the top of the hour to get "Hour Ending" values

Here is the M:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdRdjhwhEAPgq0T7HGko/gq4ymrvf40U2AYleWxr2gxfNXx/f9VPTh+bv+ZK9vXz+wS2A/PVipKyk7xqU1DPT2wVV9LOT9LKU4nvpK18e8d5qS67vfO8VFZCcXTsIFZ2PufzSl91MsDCqxqfz7KWVikM+gnmyqo8q8aiqjxrWqypSjsAOUsgkrNsF0AEB8CaBCI5CycBxFMHY761Z/82BLBf2EnR/hvpLb/9wz4Yn0AB4xWgfX0GtC8PAfbjIdDeH0PBrh9DMc6UDB3DCEoy9E8BJRE6h1GE0DGNkCRC/zjmmVU5OE9TJfBNAhEYHJs6MYxwrCq1Csd6/2ijY7m1ZxrZJBCPAzO12zs506Re4KcHkDMYLwCGEYyXgPaPIHcyXgMMYx+N2zsx06uAYcRMyeCwD0kyOOxdCE77LgTnhy8CB32cjKzKwXGaKmFfBeC032dDpcAPx6Za4HcJuPCbCJxf/r0KnPZTAC77IQGnfXkAsA/HK8BbKD0D4LdnQPz6FIhfHgPwx2MgvsthEF8Kg7dQEsLAMIKSCAMffhXB4DCKCAamsU+GOifmaarkQegSGLS/V8HgLTS1/8FZ7KOhVnz39yZ4QU1/BbG5G9zbw/5N0v9JvPXzBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Outage DateTime"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Outage DateTime Truncate to Hours", each if Time.Minute([Outage DateTime]) = 00 then [Outage DateTime] else Text.BeforeDelimiter(Text.From([Outage DateTime]),":")&":00 "&Text.End(Text.From([Outage DateTime]), 2)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Outage DateTime Truncate to Hours", type datetime}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Outage DateTime Hour Ending", each [Outage DateTime Truncate to Hours]+#duration(0,1,0,0)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Outage DateTime Hour Ending", type datetime}})
in
#"Changed Type2"

 

Activity in DAX:

-Create new column to define if a value is within the last 24 hours of the maximum outage DateTime in the dataset

Display as part of last 24 hours = IF(DateTimeData[Outage DateTime Truncate to Hours]>(MAXX(DateTimeData,[Outage DateTime Truncate to Hours])-1), TRUE(), FALSE())

 

Use the new column in DAX as a filter for the barchart

 

Here is the final view. Please feel free to send me a private message if you would like the file.

Solution Screenshot.PNG

 

Good luck!

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors