Forum Discussion
DAX measure for comparing MAX dates with conditional column filter
I'm not sure the title here best describes my predicament and I'm having trouble finding the right search terms, so forgive me if the solution to this is right under my nose here in the forums.
I am trying to sum the number of loads on a shipping schedule and filter it by only loads that have been invoiced. So I need to compare the MAX date in the DirectCosts table to the MAX date on the ShippingCalendar table, but I want to apply this filter by Cost Code so that I know how many loads of each cost code have been paid already.
Here are some sample tables:
DirectCosts
| InvoiceNumber | PaidDate | Cost Code |
| 1001 | 2/28/2022 | 160 |
| 1002 | 3/9/2022 | 165 |
| 1003 | 3/9/2022 | 160 |
| 1004 | 3/18/2022 | 166 |
| 1005 | 4/1/2022 | 165 |
| 1006 | 4/2/2022 | 165 |
| 1007 | 4/8/2022 | 160 |
| 1008 | 4/29/2022 | 160 |
| 1009 | 5/8/2022 | 166 |
| 1010 | 5/12/2022 | 161 |
| 1011 | 5/13/2022 | 161 |
| 1012 | 5/25/2022 | 165 |
ShippingCalendar
| LoadID | LoadQty | ApprovedDate | CostCode |
| 255 | 1 | 2/28/2022 | 160 |
| 256 | 1 | 3/9/2022 | 165 |
| 257 | 1 | 3/9/2022 | 160 |
| 258 | 1 | 3/18/2022 | 166 |
| 259 | 3 | 4/1/2022 | 165 |
| 260 | 2 | 4/2/2022 | 165 |
| 261 | 1 | 4/8/2022 | 160 |
| 262 | 1 | 4/29/2022 | 160 |
| 263 | 1 | 5/8/2022 | 166 |
| 264 | 2 | 5/12/2022 | 161 |
| 265 | 1 | 5/13/2022 | 161 |
| 266 | 1 | 5/25/2022 | 165 |
| 267 | 1 | 6/1/2022 | 160 |
| 268 | 1 | 6/1/2022 | 161 |
| 269 | 1 | 6/4/2022 | 160 |
| 270 | 1 | 6/5/2022 | 165 |
| 271 | 2 | 6/9/2022 | 165 |
| 272 | 1 | 6/10/2022 | 160 |
| 273 | 1 | 6/13/2022 | 160 |
| 274 | 1 | 6/13/2022 | 166 |
| 275 | 1 | 6/17/2022 | 165 |
| 276 | 4 | 6/22/2022 | 160 |
| 277 | 1 | 6/24/2022 | 165 |
| 278 | 1 | 6/27/2022 | 161 |
Both tables are joined on a CostCodes table where CostCode is the Primary Key.
I thought maybe a simple CALCULATE function would do the trick, but it's not returning any values.
PaidLoadQty =
CALCULATE(
SUM('ShippingCalendar'[LoadQty]),
FILTER(
'ShippingCalendar',
MAX('ShippingCalendar'[ApprovedDate]) <= MAX('DirectCosts'[PaidDate])
)
)
The table visual I'm looking for should look like this:
| CostCode | PaidLoadQty |
| 160 | 4 |
| 161 | 3 |
| 165 | 7 |
| 166 | 2 |
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!!
4 Replies
ManguilibeKAOResolver I
Hi,
Coud you explain the first line in your output: how do you obtain 4 in the column PaidLoadQty for the costcode 160?
Best regards.
There are 4 loads with cost code 160 on or before 4/29/22, the maximum date of a paid invoice for that cost code on the DirectCosts table.
- VahidDM
Super User
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!!
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!
