I have a table with all different machines in.
table Machines
I have a table with my Rentals agenda.
Table Rental Agenda
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
Table Lookup
-Machine Type
-Start Date
-Stop Date
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 โฆ
thanks
Solved! Go to Solution.
Likeโฆ SELECT(Machines[Serial Number],[Machine Type]=โDrillโ) - SELECT(โฆ)
How aboutโฆ Machines[Serial Number] - SELECT(Rental Agenda[serial number],AND([_THISROW].[Stop Date]>=[Start Rental],[_THISROW].[Start Date]<=[Stop Rental]))
Hi Aleksi, thanks
this is already helping a lotโฆ
any idea on how to add the machine type filter as well on it ?
Likeโฆ SELECT(Machines[Serial Number],[Machine Type]=โDrillโ) - SELECT(โฆ)
thanks, that did the job
Youโre welcome
User | Count |
---|---|
37 | |
30 | |
29 | |
20 | |
18 |