Forum Discussion
DAX measure for comparing MAX dates with conditional column filter
Hi wlandry12
I think your result table is not correct, for example the MAX date for the cost code 165 in the DirectCosts is 25-May-22 and there are 4 lines on ShippingCalendar table for that cost code with dates before 25-May-22.
BTW, try this measure:PaidLoadQty = VAR _Max_Date = CALCULATE ( MAX ( DirectCosts[PaidDate] ), ALLEXCEPT ( DirectCosts, DirectCosts[Cost Code] ) ) RETURN CALCULATE ( COUNTROWS ( ShippingCalendar ), ShippingCalendar[ApprovedDate] <= _Max_Date )Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Super UserHi wlandry12
I think your result table is not correct, for example the MAX date for the cost code 165 in the DirectCosts is 25-May-22 and there are 4 lines on ShippingCalendar table for that cost code with dates before 25-May-22.
BTW, try this measure:
PaidLoadQty =
VAR _Max_Date =
CALCULATE (
MAX ( DirectCosts[PaidDate] ),
ALLEXCEPT ( DirectCosts, DirectCosts[Cost Code] )
)
RETURN
CALCULATE (
COUNTROWS ( ShippingCalendar ),
ShippingCalendar[ApprovedDate] <= _Max_Date
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
It should be 7 because that's the sum of the LoadQty column for rows with cost code 165. If I switch the COUNTROWS function to a SUM function for the LoadQty column, the DAX measure you provided worked perfectly. Thank you!