Bar chart for displaying last 24 hours Meter Outages

[manojsv16]

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:00	10
12/13/2018  11:00:00	4
12/13/2018  10:00:00	15
12/13/2018  09:00:00	50
12/13/2018  08:00:00	20
12/13/2018  07:00:00	10
12/13/2018  06:00:00	8
12/13/2018  05:00:00	34
12/13/2018  04:00:00	56
12/13/2018  03:00:00	24
12/13/2018  02:00:00	82
12/13/2018  01:00:00	15
12/13/2018  12:00:00	10
12/12/2018  23:00:00	34
12/12/2018  22:00:00	15
12/12/2018  21:00:00	10
12/12/2018  20:00:00	4
12/12/2018  19:00:00	32
12/12/2018  18:00:00	22
12/12/2018  17:00:00	82
12/12/2018  16:00:00	24
12/12/2018  17:00:00	21
12/12/2018  16:00:00	12
12/12/2018  15:00:00	14

[CiceroBC]

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.

Good luck!