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 (difference between Open Date and today's date not including Saurdays and Sundays only), using the following:

 

Business Days = VAR calandar1 = CALENDAR('Data Source'[Open Date],TODAY())
VAR Calendar_2 = ADDCOLUMNS(calandar1,"WeekDay",WEEKDAY([Date],2))
 
RETURN (COUNTX(FILTER(Calendar_2,[WeekDay]<6),[Date]))
 
I've also created measures to get the oldest item for each category: 
Oldest Category 1 = MAXX(FILTER('Data Source',[Category 1]="1"),'Data Source'[Business Days])
 
I've already created a matrix that shows all Open items (with the detail required), but I'm not sure how to create a similar visual to show just the oldest items. My expected result is that I would have 5 rows of information. One row for each risk category that shows the item has been open the longest based on the calculated business days.
 
Any help would be greatly appreciated as I am a new user.
Thanks!
~user900
 
Need Help
  • 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.

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    user900 , Create a new column (flag column and filter for 1)

     

    Oldest Category date = if([Business Days] = MinX(FILTER('Data Source',[Category 1]=earlier([Category 1])),'Data Source'[Business Days]) ,1,0)

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

      Thanks for the response. I added the new column as suggested, but didn't quite get the results I was looking for.

      Here is a sample of my data:

      Open Date    Item   Category  Status    Business Days

      5/22/20          ABC        1         Active          279

      12/30/2020    XYZ         1        Active          121

      8/1/2019        DEF         2        Active           490

      12/31/2019    RST         2        Cancelled     382

       4/28/2020    GHI          2        Closed          297     


      I inadvertently omitted in my earlier post that for each Category, there could be items that are Closed or Cancelled. I'm thinking the new column may need to be adjusted to exclude if not Closed or Cancelled. 

      Using the flag column as suggested, I get the following Returned results, vs. Expected:

      Open Date    Item   Category  Status    Business Days  Returned   Expected

      5/22/20          ABC        1         Active          279                0               1

      12/30/2020    XYZ         1        Active          121                1                0

      8/1/2019        DEF         2        Active           490               0                1

      12/31/2019    RST         2        Cancelled     382                1                0

       4/28/2020    GHI          2        Closed          297                0                0

       

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

      Thanks for the response. I added the new column as suggested, but didn't quite get the results I was looking for.

      Here is a sample of my data:

      Open Date    Item   Category  Status    Business Days

      5/22/20          ABC        1         Active          279

      12/30/2020    XYZ         1        Active          121

      8/1/2019        DEF         2        Active           490

      12/31/2019    RST         2        Cancelled     382

       4/28/2020    GHI          2        Closed          297     


      I inadvertently omitted in my earlier post that for each Category, there could be items that are Closed or Cancelled. I'm thinking the new column may need to be adjusted to exclude if not Closed or Cancelled. 

      Using the flag column as suggested, I get the following Returned results, vs. Expected:

      Open Date    Item   Category  Status    Business Days  Returned   Expected

      5/22/20          ABC        1         Active          279                0               1

      12/30/2020    XYZ         1        Active          121                1                0

      8/1/2019        DEF         2        Active           490               0                1

      12/31/2019    RST         2        Cancelled     382                1                0

       4/28/2020    GHI          2        Closed          297                0                0

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi user900 

    "My expected result is that I would have 5 rows of information. One row for each risk category that shows the item has been open the longest based on the calculated business days."
    what do you want to get?

    -

    and how to calculate the column you expected?

    could you make a conclusion of the result you want? because, based on my understanding, the result in the picture has nothing to do with the top paragraph("My expected result is that I would have 5 rows of information...").😅

     

     

    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.

     

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

      My expected result would return a result of 1 for the Items that have the highest Business Days for each Category if the status is Not Closed or Cancelled. There are 5 Categories. All Items with a Status of Closed or Cancelled would return a result of 0 and all Items remaining in the Category with less Business Days than the one item with the highest business days would result in 0.

       

      So as there are 5 Categories, I'm expecting to end up with only 5 Items that show a result of 1. One for Category 1, one for Category 2 and so on.  

       

      My expected column for when it should be a 1 or a 0 was calculated as:

      Oldest in Category = IF([Business Days] = MINx(FILTER('Data Source',[Category]=earlier([Category])),'Data Source'[Business Days]),1,0)

      Sorry, I'm trying to explain - I am new to BI. Hope this is better. I appreciate your help.

      • v-xiaotang's avatar
        v-xiaotang
        Icon for Community Support rankCommunity Support
        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.