Calculate future values

[JMS1985]

Hello,

I have a table with vehicles (each line is an individual vehicle) and each vehicle has a date that indicates the moment the vehicle is being sold and wont be in my fleet anymore.

Plate number	Date - Sold
1111zzz	21/07/2021
2222aaa	18/08/2021

I can´t find the way to show the number of vehicles Ill have next week, month, year....

I´m sure its easy, but after reading and trying a lot, I can´t find a way to solve this. 

[v-rzhou-msft]

Hi @JMS1985 

I build a sample to have a test.

Firstly let‘s build a calendar table as below.

Calendar = 
VAR _T1 = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"MONTH",MONTH([Date]),"MONTHNAME",FORMAT([Date],"MMMM"),"YEARMONTH",YEAR([Date])*100+MONTH([Date]),"WEEKNUM",WEEKNUM([Date],2))
VAR _T2 = ADDCOLUMNS(_T1,"RANK",RANKX(_T1,[YEARMONTH],,ASC,Dense))
RETURN
_T2

Build a relationship between two tables by Date columns.

Then build measures to calcualte the count of next week, month,year.

Next WEEK Count = 
CALCULATE(COUNT('Sample'[Plate number]),FILTER('Calendar','Calendar'[Year] = YEAR(TODAY()) && 'Calendar'[WEEKNUM]= WEEKNUM(TODAY(),2)+1))

Next Month Count = 
VAR _CURRANK = CALCULATE(MAX('Calendar'[RANK]),FILTER('Calendar','Calendar'[Date] = TODAY()))
RETURN
CALCULATE(COUNT('Sample'[Plate number]),FILTER('Calendar','Calendar'[RANK] = _CURRANK+1))

Next YEAR Count = 
CALCULATE(COUNT('Sample'[Plate number]),FILTER('Calendar','Calendar'[Year] = YEAR(TODAY())+1 ))

Result is as below.

Best Regards,

Rico Zhou

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

[JMS1985]

Hello @v-rzhou-msft thank you for your help.

What I actually need is to know the number of vehicles remaining, not the number of vehicles leaving.

So if today (monday) I have 8 cars and tomorrow 2 cars leave, Ill have 6 cars remaining.

I still can´t find the way to calculate this

[S_JB]

You could include a DISTINCTCOUNT of 'plate number' in an IF statement and set to 0 if 'date - sold' is less than today. Then using 'date - sold' you could visualise the number of vehicles at a given time. See logic below:

Vehicle Count = IF('Date - Sold' < GETDATE(),0,DISTINCTCOUNT('Plate Number'))

[amitchandak]

@JMS1985 , assuming you have independent date table for selection of date

Measure =
VAR _min = Minx(allselected('Calendar'), 'Calendar'[Date]))
return
calculate(count(Table[Plate number]), filter(Table, Table[Date - Sold] >=_min))

[JMS1985]

Hello @amitchandak , thank you for your fast reply.

I´m having problems with the formula as it won´t accept "return"

"Función invocada" is a date table

"Defleet" is the column name of the date the vehicle is being sold and leaving.

FLEET =

VAR_min = MINX(ALLSELECTED('Función invocada'), 'Función invocada'[Fecha])

return

CALCULATE(COUNT(VEHICLE_VIEW[CHASSIS_NUMBER]), FILTER(VEHICLE_VIEW, VEHICLE_VIEW[DEFLEET]>=_MIN))

The "Return" sintaxis is not correct