cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Calculate future values

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.

5 REPLIES 5
Community Support

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.

Frequent Visitor

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

Resolver III

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'))

Super User

@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))

Frequent Visitor

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 =
return
CALCULATE(COUNT(VEHICLE_VIEW[CHASSIS_NUMBER]), FILTER(VEHICLE_VIEW, VEHICLE_VIEW[DEFLEET]>=_MIN))

The "Return" sintaxis is not correct

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors