Need help with Dax 🆘🙏🏻

[oveloz]

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

[daxer-almighty]

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

[oveloz]

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 😞 

[PaulDBrown]

Please share some sample data or a PBIX file

[oveloz]

Sure

https://docs.google.com/spreadsheets/d/1KtoplKgMAp2ny1vTqrbaISXUe18waUVK/edit?usp=sharing&ouid=11365...

[PaulDBrown · Accepted solution]

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

[oveloz]

Thanks!