- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
This should do what you are describing:
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!

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
04-23-2024 11:23 AM | |||
08-10-2024 08:37 PM | |||
05-09-2023 04:46 AM | |||
09-26-2024 05:32 AM | |||
08-29-2024 06:18 AM |
User | Count |
---|---|
25 | |
12 | |
11 | |
10 | |
9 |
User | Count |
---|---|
16 | |
14 | |
13 | |
12 | |
10 |