topic Re: Search for last date with certain condition in DAX Commands and Tips

Search for last date with certain condition

Alopez11 — Wed, 22 Dec 2021 19:42:54 GMT

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!

Re: Search for last date with certain condition

ValtteriN — Wed, 22 Dec 2021 22:24:37 GMT

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

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

Re: Search for last date with certain condition

VahidDM — Wed, 22 Dec 2021 23:38:59 GMT

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/

Re: Search for last date with certain condition

Alopez11 — Thu, 23 Dec 2021 13:11:56 GMT

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

Re: Search for last date with certain condition

VahidDM — Thu, 23 Dec 2021 23:50:50 GMT

Hi Alopez11

Chile is in my travel list now 🙂

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

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/