I have a table with all different machines in.
- Serial number
- Machine Type
I have a table with my Rentals agenda.
Table Rental Agenda
- Start Rental (date)
- Stop Rental (date)
- serial number
this table contains all machines that are not available … if you can not find it in this table it means that the machine is available at that specific moment.
i want to make a search table …
with the following fields
The user can make his selection of those three and then the formula looks up if there are machines available.
this search table has a virtual column where this formula is in…
Any idea on how to set this formula up ? thanks
things i have tried…
formula below gives me all machine that are not available in that time i guess when looking at the selected start & stop date compared to the existing Start and stop Rentals
select(Rental Agenda[serial number],and([Start Rental] <[_THISROW].[Start Date], [Start Rental] <[_THISROW].[Stop Date]))
select(Rental Agenda[serial number],and([Stop Rental] > [_THISROW].[Start Date], [Stop Rental] >[_THISROW].[StopDate]))
is that correct ?
i took the inverse of what i should have so these should be a list of not available machines as they come out of the agenda table
can you some how join to list without making doubles ?
This is still a list of all machine type togethers can i add this somehow in this formula so it is only giving the not available machines from a certain machine type ?
and can i than subtract those serials from the list of total machines so i get the available machines …
all help is more than welcome because i have no clue how to get there …