cancel
Showing results for
Did you mean:
Helper I

## IN vs Out per day

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.

1 ACCEPTED SOLUTION
Microsoft

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

4 REPLIES 4
Microsoft

@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.
Helper I

Please tell me if you need more details.

thank you very much in advance.

Microsoft

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

Helper I

@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

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!