Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
larencib
New Member

How vlookup last use of transportation services

Hi, i am just new in PowerBi but have some experience en Powerpivot

I would like to find out in a table the last use of a Platfom (a long vehicle, with a NumberPlate po Matricula in spanish), and calculate in wich location is .This information comes from a table that shows all movements of the platforms betweens different islands

1.PNG

 

First, i tried to determine, from each  platforms, wich is the number  of use. After that i tried to max this use to determine its last service. But i dont know how to select the last PC_centro of this MAx Use

 

I tried this formulas:

 

 

Uso = COUNTROWS(FILTER('tblextraedatospedido';tblextraedatospedido[FechaTope]<=earlier('tblextraedatospedido'[FechaTope])&&'tblextraedatospedido'[_PC_Matricula]=earlier(tblextraedatospedido[_PC_Matricula])))

 

MaxUso = max(tblextraedatospedido[Uso])

 

And i tried some different calculation (vlookup to search wich PC-centro match with Max Uso but doesnt works

 

Thanks

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@larencib

 

According to your description, you want to get the last use PC_centro within each _po_Matricula. Right?

 

You can create a measure like below:

 

Last Use PC_centro =
VAR MaxDate =
    CALCULATE (
        MAX ( 'tblextraedatospedido'[FechaTope] ),
        ALLEXCEPT ( 'tblextraedatospedido', 'tblextraedatospedido'[_po_Matricula] )
    )
RETURN
    CALCULATE (
        MAX ( 'tblextraedatospedido'[PC_centro] ),
        FILTER ( 'tblextraedatospedido', 'tblextraedatospedido'[FechaTope] = MaxDate ),
        ALLEXCEPT ( 'tblextraedatospedido', 'tblextraedatospedido'[_po_Matricula] )
    )

Regards,

 

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

@larencib

 

According to your description, you want to get the last use PC_centro within each _po_Matricula. Right?

 

You can create a measure like below:

 

Last Use PC_centro =
VAR MaxDate =
    CALCULATE (
        MAX ( 'tblextraedatospedido'[FechaTope] ),
        ALLEXCEPT ( 'tblextraedatospedido', 'tblextraedatospedido'[_po_Matricula] )
    )
RETURN
    CALCULATE (
        MAX ( 'tblextraedatospedido'[PC_centro] ),
        FILTER ( 'tblextraedatospedido', 'tblextraedatospedido'[FechaTope] = MaxDate ),
        ALLEXCEPT ( 'tblextraedatospedido', 'tblextraedatospedido'[_po_Matricula] )
    )

Regards,

 

Many thanks, works perfectly !!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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