cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ALEX13 Regular Visitor
Regular Visitor

IN vs Out per day

Hi community,

I have the following challenge:

 

With a dataset like this:

 

AvisoDate INDate OutTexto código del problema
30505001502/03/201802/03/2018Plataforma telefónica
30518399002/03/201803/03/2018Buzón de correo electrónico
30518399102/03/201803/03/2018Plataforma telefónica
30518399202/03/201805/03/2018Plataforma telefónica
30518399302/03/2018 Plataforma telefónica
30518399404/03/201807/03/2018Plataforma telefónica
30518400505/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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
saurabh_kedia_ Regular Visitor
Regular Visitor

Re: IN vs Out per day

Hi Alex,

 

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

 

T1.JPG

 

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")

 

T2.JPG

 

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]

 

T3.JPG

Regards,

Saurabh Kedia

4 REPLIES 4
Moderator v-yuezhe-msft
Moderator

Re: IN vs Out per day

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ALEX13 Regular Visitor
Regular Visitor

Re: IN vs Out per day

@v-yuezhe-msft

HI Lydia,

 

I would like to obtain the following result:

 

 INOUTAccumulative INAccumulative OUTDifference (Accumulative)
02/03/20185151-4
03/03/2018 253-2
04/03/20181 63-3
05/03/20181174-3
07/03/2018 175-2
ALEX13 Regular Visitor
Regular Visitor

Re: IN vs Out per day

Please tell me if you need more details.

thank you very much in advance.

Highlighted
saurabh_kedia_ Regular Visitor
Regular Visitor

Re: IN vs Out per day

Hi Alex,

 

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

 

T1.JPG

 

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")

 

T2.JPG

 

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]

 

T3.JPG

Regards,

Saurabh Kedia