Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jesse_Tsai
Frequent Visitor

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.

Jesse_Tsai_0-1685610085436.png

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 4
lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

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. 

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors