Forum Discussion

Alopez11's avatar
Alopez11
Regular Visitor
4 years ago
Solved

Search for last date with certain condition

Hello, I'm Agustin lopez, Horticulture from Chile working with avocado. I'm working with some irrigation data set anda  I need a hand with a DAX. I have 2 tables. 1- The theorical data table that...
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Alopez11 

    Try this:

     

     

    max date of an irrigation =
VAR _A =
    MAX( 'theorical irrigation'[Time of irrigation] )
VAR _B =
    FILTER(
        ADDCOLUMNS(
            'Real Irrigation',
            "TI", 'Real Irrigation'[Time of irrigation] / _A
        ),
        [TI] >= 2
    )
RETURN
    MAXX( _B, [Date] )

     

     

     

     

    I used this table as a Real Irrigation (Add some data for Sector 6):

     

     

     

     

     

    and the Output:

     

     

     

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

Hi Alopez11 

Try this:

 

 

max date of an irrigation =
VAR _A =
    MAX( 'theorical irrigation'[Time of irrigation] )
VAR _B =
    FILTER(
        ADDCOLUMNS(
            'Real Irrigation',
            "TI", 'Real Irrigation'[Time of irrigation] / _A
        ),
        [TI] >= 2
    )
RETURN
    MAXX( _B, [Date] )

 

 

 

 

I used this table as a Real Irrigation (Add some data for Sector 6):

 

 

 

 

 

and the Output:

 

 

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 

 

Alopez11's avatar
Alopez11
Regular Visitor
4 years ago

VahidDM thanks for your solution, It's work really well tell me when you come to chile to give u some avocados hahaha

Can i ask you one last question.
If know I want to count how many of those events of 2x irrigation happend how can I do it?

Same data set.

Thanks

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi Alopez11 

     

    Chile is in my travel list now 🙂

     

    Try this to count how many of those events of 2x irrigation happend :

    max date of an irrigation =
VAR _A =
    MAX( 'theorical irrigation'[Time of irrigation] )
VAR _B =
    FILTER(
        ADDCOLUMNS(
            'Real Irrigation',
            "TI", 'Real Irrigation'[Time of irrigation] / _A
        ),
        [TI] >= 2
    )
RETURN
    COUNTROWS( _B )

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/