Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to analyze the difference between HD (help desks) in and out for specific dates, here is an example of the data:
Asunto Start date Final date ID
No pueden contratar. | 30/01/2018 13:28:52 | 30/01/2018 13:28:53 | MRG-HD-20180130-20871 |
No pueden contratar. | 30/01/2018 08:49:14 | 30/01/2018 08:49:15 | MRG-HD-20180130-20862 |
ORDENES GENERADAS EN PUNTOS DE SUMINISTRO CESADOS 1000711105 | 29/11/2017 15:09:24 | 30/11/2017 15:09:25 | MRG-HD-20171129-20357 |
NO SALTAN LAS CONTRATACIONES DE ALTA+TRANSFORMACION | 29/01/2018 16:03:16 | 29/01/2018 16:03:17 | MRG-HD-20180129-20857 |
In the column Start date is the date when the ticket was created. In the column Final date is the date when the ticket was closed.
I want to have a comparison between HDs in vs out by date, like:
29/11/2017
HDs in: 3
HDs out: 1
Difference: 2
Any ideas on how to do it in Power BI?
Thank you very much!
Solved! Go to Solution.
Hi @ALEX13,
First, create a table including the unique start date and end date through New Table under Modeling, type the formula below.
Date = UNION ( SELECTCOLUMNS ( Table, "Date", Table[Start date] ), SELECTCOLUMNS ( Table, "Date", Table[Final date] ) )
Then, create a slicer using Date[Date], create three measure using the formulas.
HDs in = CALCULATE ( COUNT ( Table[Asunto] ), FILTER ( Table, Table[Start date] = SELECTEDVALUE ( Date[Date] ) ) ) HDs out = CALCULATE ( COUNT ( Table[Asunto] ), FILTER ( Table, Table[Final date] = SELECTEDVALUE ( Date[Date] ) ) ) Difference=ABS([HDs in]-[HDs out])
Best Regards,
Angelia
Hi @ALEX13,
First, create a table including the unique start date and end date through New Table under Modeling, type the formula below.
Date = UNION ( SELECTCOLUMNS ( Table, "Date", Table[Start date] ), SELECTCOLUMNS ( Table, "Date", Table[Final date] ) )
Then, create a slicer using Date[Date], create three measure using the formulas.
HDs in = CALCULATE ( COUNT ( Table[Asunto] ), FILTER ( Table, Table[Start date] = SELECTEDVALUE ( Date[Date] ) ) ) HDs out = CALCULATE ( COUNT ( Table[Asunto] ), FILTER ( Table, Table[Final date] = SELECTEDVALUE ( Date[Date] ) ) ) Difference=ABS([HDs in]-[HDs out])
Best Regards,
Angelia
Hi Angelia, thank you very much for the solution.
One more question, what if i have blank cells in the final date column?
are you trying to get Hours? Days?
Also, can you please provide a clear example based on the data you have posted in your post.
Hi Rocky09,
I am trying to get the data based on days.
For the data of the example:
for the day 30/01 there are 2 HDs in and 2 HDs out, i.e., in the 30/01/2018 there were two HDs created, and two HDs closed.
Tell me if you need more details-
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |