variation in and out

[ricod]

Hello

i've got an anwser previously but it not solved as i believed. 

i've a folder with xslx files containing cat and dog names. i want from 1st september 2024 calculate the variation of placements. i've tried many thinks with date table or not but i'm not able to do it specially when i filter with type (dog or cat)

can you help ?

xlsx file(s)

type	arrival	departure
dog	01/01/2025	02/01/2025
cat	01/01/2025	01/02/2025
dog	02/01/2025	02/02/2025
dog	02/01/2025	02/02/2025
dog	04/01/2025
dog	04/01/2025
dog	04/01/2025
dog	04/01/2025
dog	03/01/2024	03/02/2025
dog	03/01/2024	03/02/2025

output expected (with possibility to filter on type cat, dog, ... ) :

date	IN	OUT	variation
Jan. 2025	2	1	1
feb. 2025	2	3	0
mar. 2025	0	2	-2
apr. 2025	4	0	2

[speedramps]

Your question is too vague and confussing.

Your example input does not have any dog or cat "names".

[ricod]

hi,

it's in the xlsx files, but i should filter by using a segment on pbi

type	in	out
dog	04/12/2023
dog	10/05/2023
dog	15/11/2021
dog	10/09/2018
dog	16/06/2021
dog	15/04/2024
dog	13/11/2023
dog	19/06/2023
dog	19/03/2020
dog	12/11/2024	06/12/2024
dog	01/07/2019	31/12/2024
dog	15/11/2021
dog	05/05/2022
dog	30/05/2023
cat	17/10/2024
cat	10/10/2022
cat	01/01/2021
cat	12/06/2023
cat	14/09/2022
cat	04/12/2022
cat	20/05/2020	04/11/2024
cat	15/03/2022
cat	01/02/2019
cat	01/10/2017	31/12/2024
cat	19/04/2022
cat	17/02/2020
cat	27/05/2024	26/09/2024
cat	01/01/2023	15/11/2024
cat	02/10/2017
cat	04/09/2023
cat	17/10/2022	07/11/2024
cat	06/01/2025
cat	10/06/2024
cat	16/04/2024
cat	01/10/2020
cat	19/10/2022
cat	01/09/2016
cat	01/01/2016
cat	31/03/2016
cat	07/11/2024
cat	16/04/2020
cat	01/07/2011	31/12/2024
cat	27/05/2024	08/10/2024
cat	01/07/2019
cat	02/01/2018

[johnt75]

Create a date table and mark it as a date table. Create an active relationship from the date table date column to arrival, and create an inactive relationship to departure.

You can then create 3 measures like

Number in = COUNTROWS( 'Table' )

Number out =
CALCULATE (
    COUNTROWS ( 'Table' ),
    USERELATIONSHIP ( 'Date'[Date], 'Table'[Departure] )
)

Variance = [Number in] - [Number out]

[ricod]

on line S41 i should have -2

S42 : -1

S45 : -2

...