Forum Discussion

user900's avatar
user900
Icon for Helper II rankHelper II
5 years ago
Solved

Matrix with Oldest item

I need to create a visual to show the oldest Open items using Open Date and Category. My data source includes the Open Date and Category (1-5). I added a column to calculate the total days open (diff...
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi user900 

    I got it. Thank you for your reply.😉

    -

    Create the measure;

     

    Expected =
    VAR _maxDays = //get the highest Business Days according to 'Table'[Status]& 'Table'[Category];
        CALCULATE (
            MAX ( 'Table'[Business Days] ),
            ALLEXCEPT ( 'Table', 'Table'[Status], 'Table'[Category] )
        )
    RETURN
        IF (
            MAX ( 'Table'[Status] ) = "Cancelled"
                || MAX ( 'Table'[Status] ) = "Closed",
            0,
            IF ( MAX ( 'Table'[Business Days] ) = _maxDays, 1, 0 )//if 'Table'[Business Days] = the highest Business Days for each Category, then return 1, else 0;
        )

     

     result:

    See sample file attached bellow.

     

    Best Regards,

    Community Support Team _ Tang

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