cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wlandry12
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

InvoiceNumberPaidDateCost Code
10012/28/2022160
10023/9/2022165
10033/9/2022160
10043/18/2022166
10054/1/2022165
10064/2/2022165
10074/8/2022160
10084/29/2022160
10095/8/2022166
10105/12/2022161
10115/13/2022161
10125/25/2022165

 

ShippingCalendar

LoadIDLoadQtyApprovedDateCostCode
25512/28/2022160
25613/9/2022165
25713/9/2022160
25813/18/2022166
25934/1/2022165
26024/2/2022165
26114/8/2022160
26214/29/2022160
26315/8/2022166
26425/12/2022161
26515/13/2022161
26615/25/2022165
26716/1/2022160
26816/1/2022161
26916/4/2022160
27016/5/2022165
27126/9/2022165
27216/10/2022160
27316/13/2022160
27416/13/2022166
27516/17/2022165
27646/22/2022160
27716/24/2022165
27816/27/2022161

 

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:

 

CostCodePaidLoadQty
1604
1613
1657
1662

 

1 ACCEPTED SOLUTION
VahidDM
Super User
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:

VahidDM_0-1656372413122.png

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

View solution in original post

4 REPLIES 4
VahidDM
Super User
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:

VahidDM_0-1656372413122.png

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

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!

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

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.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors