How to pick latest date for measure

[Harry_Tran]

Hello everyone,

I have data like this

Each customer can join from multiple cities. They have to complete multiple tasks before joining. We have fromdate (like start date) and thrudate (like end date) and status to know how long from the thrudate so that we can keep track with the customer.

What I am trying to do is calculate how many customer in each city that have status for "1 year from exprired", "7 months from expired", "6 months from expired",...or "expired". I have to make sure that I only count cuctomer one time for each city.

Here is my measures: 

          _1_year_from_expire = calulate(countx(summarize(table,table[City],table[Customer]),table[Customer]), table[Status] = "1 year from expired"

Same for 7 months, 6 months,.... and expired.

The problem I have is the Customer C1 city CA_A, he has 2 status "expired" and "4 months from expired".

So is there anyway that if 1 customer have 2 thrudate for 1 city, I will pick the latest one and count this customer only one?

Thank you so much!

[v-janeyg-msft · Accepted solution]

Hi, @Harry_Tran 

According to your description, I think you need to create a calculated column and use it in filter pane to filter data first.

Like this:

Column =
IF (
    [thrudate]
        = MAXX (
            FILTER (
                ALL ( 'Table' ),
                [city] = EARLIER ( 'Table'[city] )
                    && [customer] = EARLIER ( 'Table'[customer] )
            ),
            [thrudate]
        ),
    1,
    0
)

Best Regards

Janey Guo

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

[Ashish_Mathur]

Hi,

Share an MS Excel file with your calculation logic clearly shown via formulas in that Excel file.  I will then try to translate that into DAX formulas.