Forum Discussion

manojsv16's avatar
manojsv16
Icon for Helper II rankHelper II
7 years ago
Solved

Last 24 Hours Meter Outages - Bar Chart

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
Need Help
  • themistoklis's avatar
    themistoklis
    7 years ago

    manojsv16

     

    Please have a look at the workspace on this link and let me know if this has solved your issue.

     

    I have created a new datetime column where i rounded down the datetime field.

    Rounded Hour = FLOOR(Value('Table'[Date]),1/24)

    On modelling tab make sure on data pe it is Date/Time

     

    The i used the followinf formulaL

     

    Last x hours = 
CALCULATE (
     SUM( 'Table'[Outages] ),
              FILTER (
                   ALL ('Table'),
                  'Table'[Date]>= MAX ( 'Table'[Date] ) - (23/24)
                   && 'Table'[Date] <= MAX ( 'Table'[Date] )
                           )
)

     

    Also you can add a rank field to get only the 24 hours from the latest date and then add it to the filter pane

    Rank = RANK.EQ ('Table'[Rounded Hour], 'Table'[Rounded Hour], ASC )

     

     

8 Replies

  • themistoklis's avatar
    themistoklis
    Icon for Community Champion rankCommunity Champion
    7 years ago

    manojsv16

     

    Try the following:

     

    Last x hours =
CALCULATE (
     COUNT( Table[Outages] ),
              FILTER (
                   ALL ( Table ),
                  Table[Date]
                   >= MAX ( Table[Date] ) - 1
                   && Table[Date] <= MAX ( Table[Date] )
                           )
)
    • manojsv16's avatar
      manojsv16
      Icon for Helper II rankHelper II
      7 years ago

      Hi themistoklis,

       

      Thanks a lot for the response.

       

      I tried using the above query and the filter statement results list of outages in previous and current date. 

       

      My requirement is i wanted filter the date by taking the max of date in the table (ex: 12/17/2018 10:00:00) and travel back exactly 24 hours (12/16/2018 10:00:00). Then group those date based on hours starting from  12/17/2018 10:00:00, 12/17/2018 09:00:00, ...., 12/16/2018 11:00:00, 12/16/2018 10:00:00)and calculate the count of outages.

       

      I wanted to replicate the table i used in this post.I am trying to figure out the soulute and no luck so far.

       

      I would appreciate any help on this  

       

      Thank you!!

       

       

      • themistoklis's avatar
        themistoklis
        Icon for Community Champion rankCommunity Champion
        7 years ago

        manojsv16

         

        Please have a look at the workspace on this link and let me know if this has solved your issue.

         

        I have created a new datetime column where i rounded down the datetime field.

        Rounded Hour = FLOOR(Value('Table'[Date]),1/24)

        On modelling tab make sure on data pe it is Date/Time

         

        The i used the followinf formulaL

         

        Last x hours = 
CALCULATE (
     SUM( 'Table'[Outages] ),
              FILTER (
                   ALL ('Table'),
                  'Table'[Date]>= MAX ( 'Table'[Date] ) - (23/24)
                   && 'Table'[Date] <= MAX ( 'Table'[Date] )
                           )
)

         

        Also you can add a rank field to get only the 24 hours from the latest date and then add it to the filter pane

        Rank = RANK.EQ ('Table'[Rounded Hour], 'Table'[Rounded Hour], ASC )