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
manojsv16
Helper II
Helper II

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
1 ACCEPTED SOLUTION

@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 )

 

 

View solution in original post

8 REPLIES 8
themistoklis
Community Champion
Community Champion

@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] )
                           )
)

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!!

 

 

Hi @manojsv16,

It seemed themistoklis‘s solution should meet your requirements, could you please post your desired result if possible?

1.PNG

 

Regards,

Daniel He

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

@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 )

 

 

Hi @themistoklis,

 

Thanks for the solution. I have followed the above steps and it statisfied my requirement except a couple.  I am extermly bugging you again.

 

1. Last X hours - This measures results in rolling sum of outages each hours. I would like to have the exact outages for the particular hour ( ex: 1/1/2018 12:00:00AM - 11, 1/1/2018 1:00:00AM - 25 (instead of 36), 1/1/2018 2:00:00AM - 4 (instead of 40)

2. Rank - I am not sure how to use these rank statement to get the last 24 hours. can you please help me in understanding these statement  

 

Thank you in advance!!

Outages.PNG

@manojsv16

 

If i understand correctly you want the number of outages per hour for the last 24 hours based on the latest date. Is this correct?

It wasnt clear from your initial description.

 

In this case you dont need the Last x Hours measure.

You only need the Rounded Hour and Rank columns.

 

Please see this link (Page 2).

Rank simply gets the last 24 items from the last datetime. In your case the last 24 hours.

I hope it makes sense

Hi @themistoklis,

 

I appreciate for providing the solution and it satisfied my requirement. 

 

 

Thank you!!

 

 

Hi @themistoklis,

 

Thanks for the response. I am not able to load the file in the workspace link provided below. Can you please share the updated link.

 

Regards,

Manoj Subramanian

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.