cancel
Showing results for
Did you mean:
Frequent Visitor

## 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(
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

1 ACCEPTED SOLUTION
Super User

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.

4 REPLIES 4
Super User

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.

Frequent Visitor

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!

Resolver 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.

Frequent Visitor

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.

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!