minimum value between 2 dates

[zorrro]

Hello,

I want to calcule a minimum and maximum for value that i have in table X. But i want to have the minimum and maximum between 2 dates that i have in table Y. 

Exemple : 

Table X : 

ART	Date	Value
refe1	28/11/2022
refe1	29/11/2022
refe1	30/11/2022
refe2	28/11/2022
refe2	30/11/2022

Table Y : 

ART	Date A	Date B
refe1	28/11/2022	30/11/2022
refe1	01/12/2022	04/12/2022
refe2	28/11/2022	30/11/2022
refe2	01/12/2022	04/12/2022

I tried : 

MINX(

    KEEPFILTERS(DATESBETWEEN('TableX'[dateFormatted],'TableY'[DATE A],'CBANOQ1'[DATE B])),

    CALCULATE(SUM('TableX'[Value]))

)

Thank you for your help

[Mahesh0016]

[Mahesh0016]

MaxValue = CALCULATE(MAX(X[Value]),DATEDIFF(Y[Date A],Y[Date B],DAY),ALLEXCEPT(X,X[ART]))

MinValue = CALCULATE(MIN(X[Value]),DATEDIFF(Y[Date A],Y[Date B],DAY),ALLEXCEPT(X,X[ART]))

[zorrro]

Hello,

I tried your suggestion but it's donesn't work.

I add a exemple in the tables just to be more specific for the result that i want : 

Thanks for your hepl

[v-easonf-msft · Accepted solution]

Hi, @zorrro 

Please try formula like:

minimum =
CALCULATE (
    MIN ( TableX[Value] ),
    FILTER (
        TableX,
        TableX[ART] = SELECTEDVALUE ( TableY[ART] )
            && TableX[Date] >= SELECTEDVALUE ( TableY[Date A] )
            && TableX[Date] <= SELECTEDVALUE ( TableY[Date B] )
    )
)

Your expected result seems to have wrong value, please check again.

Result:

Best Regards,
Community Support Team _ Eason

[zorrro]

TableX: 

ART	Date	Value
refe1	28/11/2022	3
refe1	29/11/2022	5
refe1	30/11/2022	6
refe1	01/12/2022	7
refe1	02/12/2022	9
refe1	03/12/2022	5
refe1	04/12/2022	8
refe2	28/11/2022	4
refe2	30/11/2022	7
refe2	02/12/2022	10
refe2	03/12/2022	17

TableY: 

ART	Date A	Date B	output
refe1	28/11/2022	30/11/2022	3
refe1	01/12/2022	04/12/2022	5
refe2	29/11/2022	30/11/2022	4
refe2	02/12/2022	03/12/2022	10

[NikhilChenna]

Hi @zorrro , Can you share the expected output result too. 

[zorrro]

@NikhilChenna 

Hello, I shared the expected output. Can you check and give me your opinion ?

Thanks

[v-easonf-msft]

Hi， @zorrro 

The fields in your formula don't seem to match the fields in your table.

Can you share your expected output to further explain your requirement?

Best Regards,
Community Support Team _ Eason

[zorrro]

@v-easonf-msft 

Hello, I shared the expected output. Can you check and give me your opinion ?

Thanks

[zorrro]

TableX: 

ART	Date	Value
refe1	28/11/2022	3
refe1	29/11/2022	5
refe1	30/11/2022	6
refe1	01/12/2022	7
refe1	02/12/2022	9
refe1	03/12/2022	5
refe1	04/12/2022	8
refe2	28/11/2022	4
refe2	30/11/2022	7
refe2	02/12/2022	10
refe2	03/12/2022	17

TableY: 

ART	Date A	Date B	output
refe1	28/11/2022	30/11/2022	3
refe1	01/12/2022	04/12/2022	5
refe2	29/11/2022	30/11/2022	4
refe2	02/12/2022	03/12/2022	10