powerBi in and out

[ricod]

Hello

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

[MFelix · Accepted solution]

Hi @ricod ,

For this you need to have a disconnected table to do the context of the date.

Add a calendar table to your model and the also add the folllowing measures:

Arrivals = COUNTROWS(FILTER('Table', 'Table'[arrival] <= MAX('Calendar'[Date]) && 'Table'[arrival] >= MIN('Calendar'[Date])))

Departures = COUNTROWS(FILTER('Table', 'Table'[departure] <= MAX('Calendar'[Date]) && 'Table'[departure] >= MIN('Calendar'[Date]) && 'Table'[departure]<> BLANK()))

variation = [Arrivals]  - [Departures]

[ricod]

yes but when i filter on dog or cat, the calculation is wrong :'(

[MFelix]

Hi @ricod ,

On the test I did the values are correct:

Since we are doing a filter on the table the cat and dog will be taken from the filter and the calculation must be correct, except if you have any other context impacting this.

[ricod]

hi,

the calcul is not the expected :