Date period filter

[jsteffe]

Hello,

I manage animal movements in buildings.

I get this table MOVEMENT : ID_movement, entry_date (in the building), exit_date (of the building), number of animals, building_id

When I choose one date, I want to get empty buildings.  (it means building with no animal, with no current movement).

What is the solution ?

Thanks for your help

Jérôme

[v-yanjiang-msft · Accepted solution]

Hi @jsteffe ,

According to your description, here's my solution.

Create a measure:

Measure =
IF (
    MAX ( 'Table'[start date] ) <= SELECTEDVALUE ( 'Date'[Date] )
        && COALESCE ( MAX ( 'Table'[end date] ), TODAY () )
            >= SELECTEDVALUE ( 'Date'[Date] ),
    1,
    0
)

Put the measure in the visual level filter and select "is 1".

After apply filter, get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jelena_L]

Hi, I didn´t get the question right, but it seams, that you would need to use IF here. 

[jsteffe]

id_movement	Building_ID	start date	end date	number of animals
1	A	01/01/2023	30/03/2023	150
2	A	15/02/2023	25/05/2023	120
3	B	01/01/2023	10/01/2023	10
4	B	02/02/2023	16/04/2023	15
5	C	01/04/2023		25

With this date, if I choose 20/04/2023, I get 2 buildings that are not empty : A and C.
How to get these 2 buildings ?