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

New Member

## Need help with Dax ๐๐๐ป

Hi guys, for days I have been trying to recreate a formula in Excel with Dax but I have not had any luck

The formula is a Vlookup inside an iferror that shows me the date of the last meal when the Lot is equal to the current one

then look inside Column E for the value equal to the current -1 that would indicate the last feeding time

and it returns the correct value to me:

now i need to do the exact same thing in DAX, If you know of any way to do it, I appreciate your help

1 ACCEPTED SOLUTION
Community Champion

With this model

and this measure

``````Previous date for Lote =
VAR Lote =
MAX ( 'Dim Lote'[Lote] )
VAR CalcDate =
MAXX (
FILTER (
ALL ( 'Table' ),
'Table'[Fecha Comida] < MAX ( 'Table'[Fecha Comida] )
&& 'Table'[Lote] = Lote
),
'Table'[Fecha Comida]
)
RETURN
CALCULATE (
MAX ( 'Calendar Table'[Date] ),
FILTER ( ALL ( 'Calendar Table' ), 'Calendar Table'[Date] = CalcDate )
)
``````

You will get

I've attached the sample PBIX file

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

6 REPLIES 6
Solution Sage

I suspect this is to be a calculated column. Why don't you do this where it should be done - in Power Query? PQ lets you do such things with ease, without any coding, just by using mouse clicks...

New Member

The registration is for feeding, so I need to know how much time has passed since the last feeding, example March 20 and I want to know how many days have passed since that date when the batch is the same as the one selected, the idea of โโthat formula is that it measures, since A record was added with a certain batch back and save that date and then do the same process because if I do it dynamic, that formula would only see the last date and it is not what I am looking for. In the event that the last registration is on December 20, the difference would be very large and unreal ๐

Community Champion

Please share some sample data or a PBIX file

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

New Member
Community Champion

With this model

and this measure

``````Previous date for Lote =
VAR Lote =
MAX ( 'Dim Lote'[Lote] )
VAR CalcDate =
MAXX (
FILTER (
ALL ( 'Table' ),
'Table'[Fecha Comida] < MAX ( 'Table'[Fecha Comida] )
&& 'Table'[Lote] = Lote
),
'Table'[Fecha Comida]
)
RETURN
CALCULATE (
MAX ( 'Calendar Table'[Date] ),
FILTER ( ALL ( 'Calendar Table' ), 'Calendar Table'[Date] = CalcDate )
)
``````

You will get

I've attached the sample PBIX file

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

New Member

Thanks!