Reply
Alopez11
Regular Visitor

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 is supouse what has to happend. (Name: Base de dato Agricola)
2- The data table with the data that really happend. (Name: Informacion Agricola)

I have the following data set

Table 1 (theorical irrigation)
Sector     Time of irrigation(min)
   5                 80
   6                100

Table 2 (Real Irrigation)
Sector      Date         Time of irrigation
 5           20-11-2021       80
 5            22-11-2021     160
 5             01-12-2021     80


In the dax, I need to see when was the last day (max date) of an irrigation that has a 2x time of irrigation from the Theorical data (Table 1). i.e. the dax should give me back the date of 22-11-2021  of the data set as example. I use as a key the Sector.

Thanks!



1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

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):

 

VahidDM_4-1640216313113.png

 

 

 

 

and the Output:

 

VahidDM_3-1640216302476.png

 

 

 

 

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/

 

 

View solution in original post

4 REPLIES 4
VahidDM
Super User
Super User

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):

 

VahidDM_4-1640216313113.png

 

 

 

 

and the Output:

 

VahidDM_3-1640216302476.png

 

 

 

 

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

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/

 

 

ValtteriN
Super User
Super User

Hi,

This should do what you are describing:


Irrigation Maxdate =
var selection = SELECTEDVALUE('Irrigation T'[Irrigation])
var sector = MAX(Irrigation[Sector]) return
MAXX(filter(Irrigation,Irrigation[Irrigatio]=2*selection&&Irrigation[Sector]=sector),Irrigation[date])

ValtteriN_0-1640211843881.png


 I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)