Count rows with different conditions related to dates

[Anonymous]

Hello everyone,

I am new to Power BI and I am stuck right now. I try to count rows with different conditions. Here is how my data looks like:

I already have a diagram where data from a different table is visualized: 

What I am trying to do now is to add a line chart with the number of vehicles on a certain day. On the first screenshot above, the table "Disposition" can be seen with the columns "Fahrzeugeingang_IST", this is the time at which a vehicle was delivered. "Fahrzeugrückgabe_IST" is the time at which the vehicle was returned. In addition, we have the column "Location".

I would now like to evaluate how many vehicles were there on a certain date at a certain location.
I have created a new date table and have tried to count the rows using the "COUNTROWS" function. However, I have not managed to include the date as a condition (count if the date is greater than "Fahrzeugeingang_IST", less than "Fahrzeugrückgabe_IST" and location "P1").

My idea was to insert a column with the name of every location and then to count the number of vehicles: 

If you need any further information, please let me know. 

Thanks in advance for all your help!

[v-xiaotang]

Hi @Anonymous 

>> Seems legit but how can I also take the location (P1, P2, P3, ...) into credit? Do I need a new date table for every location or can I make this somehow as a condition in a row

if the relationship is one to many,

maybe you can try this, 

Best Regards,

Community Support Team _Tang

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

[amitchandak · Accepted solution]

@Anonymous , it seem like you need something like Active Employee in HR, refer blog of file

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

@amitchandak thank you for sharing the solution with me! 

Seems legit but how can I also take the location (P1, P2, P3, ...) into credit? Do I need a new date table for every location or can I make this somehow as a condition in a row?