IN vs Out per day

[ALEX13]

Hi community,

I have the following challenge:

With a dataset like this:

Aviso	Date IN	Date Out	Texto código del problema
305050015	02/03/2018	02/03/2018	Plataforma telefónica
305183990	02/03/2018	03/03/2018	Buzón de correo electrónico
305183991	02/03/2018	03/03/2018	Plataforma telefónica
305183992	02/03/2018	05/03/2018	Plataforma telefónica
305183993	02/03/2018		Plataforma telefónica
305183994	04/03/2018	07/03/2018	Plataforma telefónica
305184005	05/06/2018		Plataforma telefónica

I need to create a Slicer showing:

-Number of "Avisos" per Date IN.

-Number of "Avisos" per Date OUT.

-Accumulative number of "Avisos" IN per day.

-Accumulative number of "Avisos" OUT per day.

-Difference between Accumulative number of Avisos IN and OUT.

Thank you in advance.

[v-yuezhe-msft]

@ALEX13,

Could you please describe more details about your scenario? What field do you use to create slicer?

For those blank Date Out values, how do you display the number and accumulative numbers of "Avisos"? Moreover, about the last requirement (Difference between Accumulative number of Avisos IN and OUT), what data would you like to show?

Regards,

Lydia

[ALEX13]

Please tell me if you need more details.

thank you very much in advance.

[saurabh_kedia_ · Accepted solution]

Hi Alex,

First in the edit query unpivot Date IN & Date OUT column, which will result in the below table:

Then make a summarised columns which only contains date:

Date = SUMMARIZE(Table1,Table1[Value])

Link this table and the main table on value (Date column)

Make two columns for date in & out count:

IN = CALCULATE(COUNT(Table1[Attribute]),Table1[Attribute]="DATE In") 

OUT = CALCULATE(COUNT(Table1[Attribute]),Table1[Attribute]="DATE OUT")

Finally create two measures for running total and then take the difference for the last column.

IN running total in Value =
CALCULATE(
SUM('Date'[IN]),
FILTER(
ALLSELECTED('Date'[Value]),
ISONORAFTER('Date'[Value], MAX('Date'[Value]), DESC)
)
)

OUT running total in Value =
CALCULATE(
SUM('Date'[OUT]),
FILTER(
ALLSELECTED('Date'[Value]),
ISONORAFTER('Date'[Value], MAX('Date'[Value]), DESC)
)
)

Difference = [OUT running total in Value]-[IN running total in Value]

Regards,

Saurabh Kedia

[ALEX13]

@v-yuezhe-msft

HI Lydia,

I would like to obtain the following result:

	IN	OUT	Accumulative IN	Accumulative OUT	Difference (Accumulative)
02/03/2018	5	1	5	1	-4
03/03/2018		2	5	3	-2
04/03/2018	1		6	3	-3
05/03/2018	1	1	7	4	-3
07/03/2018		1	7	5	-2