Combining a Calendar with Date Driven Data

[nierodzt]

Hello,

New user to the Power B.I Community. I was wondering if I could get some help on a problem.

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.

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.  The second table is a list of all the days of 2017.

Is there anyway 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 09/23/2017-10/09/2017 I would like to see a blank space associated with those dates.

Vehicle Information        Dates of 2017

[v-xjiin-msft]

Hi @nierodzt,

In your scenario, you want to show corresponding rented vehicle information of each day in 2017. Right?

To achieve your requirement, I think you just need to create a relationship for your two tables (Vehicle Information and Calendar table), then put the date column of Calendar table and the vehicle information into one same table visual. Please refer:

Thanks,
Xi Jin.

[dramus]

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

[nierodzt]

I would have to know the vehicle that was not rented

[dramus]

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]

Take a look at the DAX function NATURALLEFTOUTERJOIN().

I created the following table, which I think gives you what you want.

All Dates and Vehicles = NATURALLEFTOUTERJOIN('calendar','Vehicle Information')

There needs to be a link between the Calendar and Vehicle Information tables, so that the function knows  what to join on.

[dramus]

Hmm....Just added a second Equipment ID and found that it's not working exactly as needed. 

I'll keep working on it.

[dramus]

I have it working. I had to jump through a couple of hoops.

First I created a table with just the Equipment ID's in it.

I then used the CROSSJOIN() function to create a cartesian product table of the dates and the equipment.

All Dates And Equipment = CROSSJOIN('calendar','Equipment')

I then added a column to the "All Dates and Equipment" table:

Was Rented = if(isblank(LOOKUPVALUE('Vehicle Information'[Charge Date],'Vehicle Information'[Charge Date],[Date],'Vehicle Information'[Equipment Id],[Equipment])),"No","Yes")

I think this gives you what you need.

PM me with your email address if you would like the PBIX file.

[nierodzt]

Close to 900 😞

I will give it a try. thanks