Combining a Calendar with Date Driven Data

Question

Hello,&nbsp;New user to the Power B.I Community. I was wondering if&nbsp;I could get some help on a problem.&nbsp;I work for a rental company and the goal is to combine calendar dates with dates vehicles are rented to determine which days the company did not rent any vehicles.&nbsp;I have two tables. The first, contains the days vehicles are rented with location and other business data (Column A contains vehicle number, B contains the date, C the location, ETC). There are over 900 vehicles with multiple days rented for each.&nbsp;&nbsp;The second table is a list of all the days of 2017.&nbsp;Is there anyway&nbsp;I can merge or combine the data so that each day in 2017 has it own vehicle associated with it? Example, since 100114 was not used on 09/16/2017, 09/17/2017, 09/21/2017, and&nbsp;09/23/2017-10/09/2017 I would like to see a blank space associated with those dates.&nbsp;&nbsp;Vehicle Information&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dates of 2017&nbsp;

dramus · Answer

Do you need to know which vehicles were not rented, or just the number that were not rented on a particular day?&nbsp;&nbsp;&nbsp;

nierodzt · Answer

I would have to know the vehicle that was not rented

dramus · Answer

Yuck. How many vehicles are we talking about? Creating a column for each would seem to not be a viable solution. Which was my first thought.

dramus · Answer

Take a look at the DAX function NATURALLEFTOUTERJOIN().&nbsp;I created the following table, which I think gives you what you want.&nbsp;All Dates and Vehicles = NATURALLEFTOUTERJOIN('calendar','Vehicle Information')&nbsp;There needs to be a link between the Calendar and Vehicle Information tables, so that the function knows&nbsp; what to join on.

nierodzt · Answer

Close to 900 :(&nbsp;I will give it a try. thanks

Forum Discussion

Combining a Calendar with Date Driven Data

8 Replies

Related content

Tables combine

Metadata driven pipeline

What’s the best for Metadata driven Pipeline?

Calendar Slicer with Different Business Calendars

Metadata Driven DAGs in Airflow