cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 =
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

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors