New items from start of year

[Ritaf]

Hi,

I have a table of cars that we own at our fleet vehicles, every month, a part of cars are replaced by new and we purchasing new cars also. The table of cars seems like a table below. How can I calculate the new cars that we owning by the granularity of the month and year?  (The model have a date table with the active relationship "by date of the report" because of other measures, so the calculation have to deal with use relationship or another solution too) . Thank you, Rita

date of report	date of purchase	car id
01/01/2020	01/12/2019	1111
01/02/2020	01/01/2020	5555
01/03/2020	01/01/2020	666

[gdarakji · Accepted solution]

You need to create an inactive relationship between the date table and "Date of purchase". After that, you can write the following measure to get the count:
CountOfPurchasedCars = CALCULATE(COUNT(Cars[Car id]),USERELATIONSHIP(Dates[Date],Cars[date of purchase]))

Note that I assumed the name of the table you posted is "Cars" and your dates table is called "Dates". Please update those in the measure if my assumptions are not correct.

[Ritaf]

That exactly what I did, I thought I need different logic, but it's good enough, I will stay with it.

Thank you.

[amitchandak]

@Ritaf , with date table joining to date of report, should give you new cars every month with help from month and year columns?

Can you share sample output in table format?