Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Harry_Tran
Helper III
Helper III

How to pick latest date for measure

Hello everyone,

I have data like this

Harry_Tran_0-1622154217160.png

 

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!

 

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

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
)

 

v-janeyg-msft_0-1622537646895.png

 

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.

View solution in original post

2 REPLIES 2
v-janeyg-msft
Community Support
Community Support

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
)

 

v-janeyg-msft_0-1622537646895.png

 

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.