Skip to main content
cancel
Showing results forΒ 
Search instead 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

Reply
oveloz
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

 

oveloz_0-1629992799202.png

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

oveloz_1-1629992850121.png

and it returns the correct value to me:

oveloz_2-1629993003539.png

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

With this model

model.JPG

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

result.JPG

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
daxer-almighty
Solution Sage
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...

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
Community Champion
Community Champion

Please share some sample data or a PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






With this model

model.JPG

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

result.JPG

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thanks! 

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors