Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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 )
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?
Regards,
Daniel He
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!!
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
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |