Forum Discussion
need help with DAX calculation
Hi everyone, need help here!
I have a trip record table, where it shows DATE, PLATE, TRIP_CODE and I want that when selecting a date (in a filter visual) it results in the list of vehicles that travel and the last date they previously traveled (yellow box)
Hi Gonast
If you want a measure, check this:
ULTIMA FECHA = VAR vDate = SELECTEDVALUE(SalidasLima[FECHA]) RETURN CALCULATE(MAX(SalidasLima[FECHA]), ALLEXCEPT(SalidasLima,SalidasLima[PLACA1]),SalidasLima[FECHA]<vDate)Model:
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
5 Replies
Hi Gonast - you can create a calculated column that determines the last travel date for each vehicle before the selected date.
ULTIMA FECHA =
CALCULATE(
MAX('TripRecord'[FECHA]),
FILTER(
'TripRecord',
'TripRecord'[PLACA1] = EARLIER('TripRecord'[PLACA1]) &&
'TripRecord'[FECHA] < EARLIER('TripRecord'[FECHA])
)
)This column will display the last travel date for each vehicle before the current row's date.
Hope this works. please check
Hi Gonast ,
Use the following DAX to create a Calculated Column for "Ultima Fecha":
ULTIMA FECHA = CALCULATE( MAX(TripRecord[FECHA]), TripRecord[FECHA] < EARLIER(TripRecord[FECHA]), TripRecord[PLACA1] = EARLIER(TripRecord[PLACA1]) )Thanks for your answers, but it didn't work. Let me explain, for this exercise I have 2 tables. A table called "SalidasLima" where I have the columns FECHA, PLACA1, COD_VIAJE; and the other table is a date table called "Date".
For this exercise I am using a filter with Date where I can select any date, and as a result it should give me the table of the initial image, where the FECHA column corresponds to the filter selection, the PLACA1 column tells me the identification of the vehicle that leaves on the date I selected and the yellow column should indicate the last previous date that the selected vehicle left.
Hi Gonast
If you want a measure, check this:
ULTIMA FECHA = VAR vDate = SELECTEDVALUE(SalidasLima[FECHA]) RETURN CALCULATE(MAX(SalidasLima[FECHA]), ALLEXCEPT(SalidasLima,SalidasLima[PLACA1]),SalidasLima[FECHA]<vDate)Model:
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!Thanks you very much!
