Forum Discussion
How to visualize matrix by scrolling date slicer
Hello everyone, I have a column in Power BI Desktop that shows the downtime of my server, and it is the number of minutes. And another column is to yield the uptime percentage. The formula is Upt...
Thank you for your response.
Data:
| Responsibility | Downtime(Minutes) | Uptime Percentage |
| Others | 89 | 98.67% |
| Others | 84 | 98.67% |
| Others | 140 | 98.67% |
| TE03 Team | 283 | 99.34% |
| Others | 60 | 98.67% |
| Others | 98 | 98.67% |
| Others | 110 | 98.67% |
| Plan | 93 | 99.78% |
DAX of UpTime Percentage:
IF('Work Items - All history by month'[Who is Responsible for]="TE03 Team",
1-CALCULATE(sum('Work Items - All history by month'[DownTime]), FILTER('Work Items - All history by month', 'Work Items - All history by month'[Who is Responsible for]="TE03 Team"))/43200,
IF('Work Items - All history by month'[Who is Responsible for]="Others",
1-CALCULATE(sum('Work Items - All history by month'[DownTime]), FILTER('Work Items - All history by month', 'Work Items - All history by month'[Who is Responsible for]="Others"))/43200,
IF('Work Items - All history by month'[Who is Responsible for]="Plan",
1-CALCULATE(sum('Work Items - All history by month'[DownTime]), FILTER('Work Items - All history by month', 'Work Items - All history by month'[Who is Responsible for]="Plan"))/43200)))
And I use Matrix and Time Sclicer to visualize the data. In the matrix, there's a column "Period of Time" to show a period of time in minutes which is 43200 (a month in minutes as default).
What I want to achieve:
I hope I can get the start date and the end date when I scroll the time slicer, and it can convert the period into minutes to show on the column "Period of Time" and put it into the DAX from the above to calculate the percentage I want to observe.
Take "TE03 Team" from the data as an example, the downtime is 283 minutes. I scroll the time slicer to get a start date of 2022/10/1and an end date of 2022/10/7 which is 7 days, 10080 minutes in total (60x24x7), and 10080 will show on the column "Period of Time". At the same time, I can use the DAX to get the Uptime Percentage. It will be (10080-1503)/10080x100%=97.19%.
Is there any possibility that I can make the start date and the end date as variables to put them into DAX, and it will be like [("end date" - "start date")-downtime]/("end date" - "start date")x100%. But how can I achieve that and how to convert them into minutes simultaneously?
Take "TE03 Team" from the data as an example, the downtime is 283 minutes. I scroll the time slicer to get a start date of 2022/10/1and an end date of 2022/10/7 which is 7 days, 10080 minutes in total (60x24x7), and 10080 will show on the column "Period of Time". At the same time, I can use the DAX to get the Uptime Percentage. It will be (10080-1503)/10080x100%=97.19%.
Is there any possibility that I can make the start date and the end date as variables to put them into DAX, and it will be like [("end date" - "start date")-downtime]/("end date" - "start date")x100%. But how can I achieve that and how to convert them into minutes simultaneously?
If there's anything that I miss, please let me know. Thank you for being so helpful.
lbendlin
Super User
Super UserYour sample data is missing the start datetime and end datetime columns
Thank you for your response. I don't have the start datetime and the end datetime columns now. The period of time is a month in minutes as default, so I hope I can catch the two dates when I scroll the date slicer to calculate.