Forum Discussion
minimum value between 2 dates
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
Hi, Anonymous
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
10 Replies
Hi， Anonymous
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 _ EasonTableX:
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
Hi Anonymous , Can you share the expected output result too.
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
- 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]))
Hi, Anonymous
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
