Forum Discussion
Helper IIN 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.
Thank you in advance.
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
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
ALEX13Anonymous
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 - ALEX13
Please tell me if you need more details.
thank you very much in advance.
- saurabh_kedia_
Microsoft Employee
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
