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 Upt...
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

    • Jesse_Tsai's avatar
      Jesse_Tsai
      Frequent Visitor
      3 years ago

      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.