Forum Discussion

JMS1985's avatar
JMS1985
Frequent Visitor
5 years ago

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 numberDate - Sold
1111zzz21/07/2021
2222aaa18/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. 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      JMS1985
      Frequent Visitor
      5 years ago

      Hello Anonymous 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

       

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      JMS1985
      Frequent Visitor
      5 years ago

      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
  • S_JB's avatar
    S_JB
    Resolver III
    5 years ago

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