Forum Discussion
creating data by shifts
Hi
in powerbi desktop, I need to create a graph showing how many phone calls are taken by shifts (7a-3p, 3p-11p, etc) from jan 2022-current date.
Is it a measure i have to create, and if so, how would i create that exactly? (Total calls offered means calls that come into us)
thanks!
Best optimal solution is to have time table with bins.
Say, Table: Dim Time and columns: Time, Shift Bin
Column: Time in every minute or every hour to your granularity in the transaction table
Column: Shift Bin the text you want to classify the range
Quick and easy solution is
1. Add this column to your table.
DAX formula takes the time and convert it and then get the hour from it.
Using the hours, we are bin / grouping them to the requirements
TODO: Adjust the hours used in binning and text to your needs
Shift Bin Time = var _hour=HOUR( Format('Table'[Time End], "hh:mm:ss") ) Return SWITCH(TRUE(), _hour>=6&&_hour<=12,"Morning", _hour>12&&_hour<=17,"Noon", _hour>17&&_hour<=22,"Evening", _hour>22||_hour<=6,"Night")2. Use this column and create table or charts
Hope this helps!
1 Reply
sevenhillsSuper User
Best optimal solution is to have time table with bins.
Say, Table: Dim Time and columns: Time, Shift Bin
Column: Time in every minute or every hour to your granularity in the transaction table
Column: Shift Bin the text you want to classify the range
Quick and easy solution is
1. Add this column to your table.
DAX formula takes the time and convert it and then get the hour from it.
Using the hours, we are bin / grouping them to the requirements
TODO: Adjust the hours used in binning and text to your needs
Shift Bin Time = var _hour=HOUR( Format('Table'[Time End], "hh:mm:ss") ) Return SWITCH(TRUE(), _hour>=6&&_hour<=12,"Morning", _hour>12&&_hour<=17,"Noon", _hour>17&&_hour<=22,"Evening", _hour>22||_hour<=6,"Night")2. Use this column and create table or charts
Hope this helps!