How to compare values in and out by dates

[ALEX13]

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!

[v-huizhn-msft · Accepted 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

[ALEX13]

Hi Angelia, thank you very much for the solution.

One more question, what if i have blank cells in the final date column?

[rocky09]

@ALEX13

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.

[ALEX13]

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-