Forum Discussion

oveloz's avatar
oveloz
New Member
5 years ago
Solved

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

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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

6 Replies

  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    5 years ago

    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's avatar
      oveloz
      New Member
      5 years ago

      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 😞