Calculate timedifference between slicer which contains 2 rows of data

[Yasin_123]

Hello guys, 

I hope you all had a pleasent start into the new year 🙂

I have two problems in Power BI:

1. I have a table containing the start- and enddate of a certain failure of a machine (see picture below). In a "between" slicer I want to be able to select the start and the end date of my observation period. But I don't know how to get them into one slicer. The picture below shows, that I am right now only able to put the start date or the end date of the malfunction into the "between" slicer. Do you guys know a solution concerning this problem?

2. If you guys know the answer to the first problem you may proceed to the next one. After getting the slicer with the two different dates I need to calculate the timedifference between those two dates which were put into the slicer.

E.g.: If my start date is the 1/15/2024 and my end date is 1/20/2024 the days between those dates are 5 (or 6 if you count the 15th too)

Do you guys know a solution to this? I need the time difference to use for further calculations.

I hope you can help me in this matter 🙂

Thank you for your hep in advance!

Kindly,

Yasin

[v-tangjie-msft]

Hi @Yasin_123 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.

(2) We can create a date table.

Date Table = CALENDAR(FIRSTDATE('Table'[Start of malfunction]),LASTDATE('Table'[End of malfunction]))

(3) We can create measures. 

Flag = IF( MAX('Table'[Start of malfunction]) >= MIN('Date Table'[Date]) && MAX('Table'[End of malfunction]) <= MAX('Date Table'[Date]),1,0)

timedifference = DATEDIFF(MIN('Table'[Start of malfunction]),MAX('Table'[End of malfunction]),DAY)

timedifference for slicer = DATEDIFF(MIN('Date Table'[Date]),MAX('Date Table'[Date]),DAY)

(4) Place [Flag=1] on visual object screening and then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Yasin_123]

Hello @v-tangjie-msft and @lbendlin,

thank you for your quick response. To understand my problem better I provide you guys with some test data.

I want to calculate the MTTR (Mean Time to Repair), MDT (Mean Downtime) and MTBF (Mean Time Between Failure) for the machines (Equipment in data) of our company. 

MDT= SUM(Closed Ticket Date - Opened Ticket Date)/ Number of Tickets of the Machine in the observation period
MTTR= SUM (Closed Order Date - Opened Order Date)/Number of Orders of the Machine in the observation period
MTBF= (Observation Period-Failure time of Machine)/Number of Failures of the Machine in the observation period

When I am saying observation Period, I want to give a Startdate and a Enddate, via the slicer where all Tickets are considered in the calculation if their Start Date is over the slicer Start date and under the slicer Enddate.

The difference in Time between start and end date should be used to calculate the obeservation period. With which I can then calculate my needed KPIs.
It could happen that I will add also the time but this I should be able to do by myself if you guys can tell me how to solve this problem. 

Anonymized Data for Help.pbix

Once the table should show me the KPI for the specified obeservation date and then also the dates per month (which should be easier).
Thanks for your help in advance!

Kindly,
Yasin

[v-tangjie-msft]

Hi @Yasin_123 ,

I don't have permission to open your link, please re-grant permission to link and share.

Update the measure.

Flag = IF( MAX('Table'[Start of malfunction]) >= MIN('observation Period Table'[Date]) && MAX('Table'[Start of malfunction]) <= MAX('observation Period Table'[Date]),1,0)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Yasin_123]

Hello @v-tangjie-msft,

is there another way to share the data? Somehow my company won't give access to you via onedrive. And uploading data to the forum is only possible if you are from support or a super user. 
Best Regards,

Yasin

[v-tangjie-msft]

Hi @Yasin_123 ,

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Neeko Tang

[Yasin_123]

Hi @v-tangjie-msft,

as it is not possible to send you the data I will make pictures and tables: 
Modell is the following:

All Tickets Table: 
Is merge of closed and opened Tickets and connected to other table via tickets

All Orders Table:
Is merge of closed an opened orders and connected to other table via orders
Date Table:

Normal Date Table with dates etc.

Closed Orders Table:

Start of order	End of order	Order Code	Ticket Code	Equipment	Machine Failure	Failure Time
27.06.2023	22.07.2023	10008	7	400001	No	0
25.12.2023	28.12.2023	10011	8	400021	Yes	12
10.11.2023	18.12.2023	10021	9	400111	Yes	0
10.08.2023	20.08.2023	10033	10	400334	No	0
01.01.2023	01.01.2023	10035	11	400245	Yes	8
23.12.2023	29.12.2023	10012	8	400021	Yes	12
01.07.2023	15.07.2023	10015	7	400001	No	0

Open Orders Table:

Start of order	Order Code	Ticket Code	Equipment	Machine Failure	Failure Time
01.03.2024	10001	1	400001	No	0
05.01.2024	10002	2	400022	Yes	5
10.01.2024	10005	3	400102	No	0
15.01.2024	10007	4	400304	Yes	10
03.01.2024	10020	2	400022	Yes	5

Closed Tickets Table:

Start of malfunction	End of malfunction	Ticket Code	Order Code	Equipment	Machine Failure	Failure Time
25.06.2023	28.07.2023	7	10008	400001	No	0
22.12.2023	30.12.2023	8	10011	400021	Yes	12
10.11.2023	20.11.2023	9	10021	400111	Yes	0
08.08.2023	10.08.2023	10	10033	400334	No	0
01.01.2024	02.01.2024	11	10035	400245	Yes	8

Open Tickets Table:

Start of malfunction	Ticket Code	Order Code	Equipment	Machine Failure	Failure Time
01.01.2024	1	10001	400001	No	0
02.01.2024	2	10002	400022	Yes	5
10.01.2024	3	10005	400102	No	0
12.01.2024	4	10007	400304	Yes	10
15.01.2024	5	10010	400256	Yes	7

[v-tangjie-msft · Accepted solution]

Hi @Yasin_123 ,

We can create a date table as a slicer. Note that this date table does not have any modeling relationship with the other tables.

Date Table = CALENDAR(Date(2023,1,1),Date(2024,12,31))

We can create measures.

Flag = IF( MAX('Closed Orders Table'[Start of order]) >= MIN('Date Table'[Date]) && MAX('Closed Orders Table'[Start of order]) <= MAX('Date Table'[Date]),1,0)

Flag2 = IF( MAX('Open Orders Table'[Start of order]) >= MIN('Date Table'[Date]) && MAX('Open Orders Table'[Start of order]) <= MAX('Date Table'[Date]),1,0)

Flag3 = IF( MAX('Closed Tickets Table'[Start of malfunction]) >= MIN('Date Table'[Date]) && MAX('Closed Tickets Table'[Start of malfunction]) <= MAX('Date Table'[Date]),1,0)

Flag4 = IF( MAX('Open Tickets Table'[Start of malfunction]) >= MIN('Date Table'[Date]) && MAX('Open Tickets Table'[Start of malfunction]) <= MAX('Date Table'[Date]),1,0)

Place [Flag=1] on visual object screening .

If this doesn't help, give your expected results.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[lbendlin]

It is as easy as using VALUES(<slicer date column>)  as your filter.  Or if you really need to know the range, use MIN and MAX against that slicer date column.