Matrix with Oldest item

[user900]

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

[v-xiaotang]

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]

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 · Accepted solution]

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.

[user900]

Thanks again for your time. I used the suggested measure but it didn't seem to give me the result I was expecting based on Active items with the highest # of Business Days. I added a new column to see which items are returning as 1 vs 0.  From the sample below you'll see items that are not active are returning 1 based on the suggested solution. All Closed and Cancelled regardless of Category should be 0.

Category  Status    Business Days  Result: Suggested Solution

STU        1    Closed     728                   1

A10        1    Closed     599                   1

A20        2    Closed     909                   1

A26        2    Cancelled 513                  1        

A29        2    Cancelled 540                  1

Then for Active items, I'm looking for the one item in each category that has the highest # of business days. The sample below shows what returned using the suggested solution (but as a newly created column). I'm expecting 1 for item A46 and A90 vs 0 based on these having the highest # of business days in their category. 

  Item    Category  Status    Business Days  Result: Suggested Solution

 A46         1           Active          284                0             

 A90         2           Active          495                0

A103        2           Active          254                0

A104        1           Active          224                0

[v-xiaotang]

Hi @user900 

I've tested with my sample again. and it still works OK. it seems my measure is right...

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.

[amitchandak]

@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]

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]

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