Forum Discussion

Jesse_Tsai's avatar
Jesse_Tsai
Frequent Visitor
3 years ago

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 Uptime= [(a period of time)-(downtime)]/(a period of time)x100%, and it will be a percentage of uptime at a specific period.

For now, I can only observe the percentage with the default time of a month in minutes.

Here's what I want to achieve. Take Plan from the above column Responsibility as an example, when I scroll the date slicer to make a date start from 2022/10/1 to 2022/10/7 which is 7 days, 10080 minutes in total (60x24x7), 10080 will show on the column Period of Time, therefore I can use the formula to get the Uptime Percentage. It will be (10080-1503)/10080x100%=85.1%. If it's possible, I want to get a dynamic uptime percentage by scrolling the date slicer.

I've been thinking about how to achieve this, since every column has to be defined before visualizing, I didn't figure it out.
If anyone has any ideas, please share them with me. Thank you for your help!

4 Replies

    • Jesse_Tsai's avatar
      Jesse_Tsai
      Frequent Visitor
      3 years ago

      Thank you for your response. 
      Data:

      ResponsibilityDowntime(Minutes)Uptime Percentage
      Others8998.67%
      Others8498.67%
      Others14098.67%
      TE03 Team28399.34%
      Others6098.67%
      Others9898.67%
      Others11098.67%
      Plan9399.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?
       
      If there's anything that I miss, please let me know. Thank you for being so helpful. 
      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        Your sample data is missing the start datetime and end datetime columns