PowerBi DIstinct and IF formula

[Anonymous]

Hi everyone,

I'm a bit stuck with a report I'm trying to build.

I have the below columns where I'm trying to find for how many days it was outstanding.

I've already created new columns for this in PBI where if outstanding (Status still open and I calculate Report Date - Created date) between 1-4 working days results in "good", if 5-10 working days - "amber" and everything higher is "red"

The problem I have is :

                       - For example, the invoice with ID 0123, created on 05/06/2023, shows on every report for 05/06, 06/06, 07/06, 08/06 as "good" as in all those days it was created within 4 working day, however on 09/06/2023 it will show as "amber".

               Assuming I only have 1 invoice on my report (ID 0123), and if I filter the report for last week, I will see that 1 invoice was "good", 1 invoice was "amber", where I would like to see that last week I only had 1 invoice and that was in "amber"

Essentially if it was good and now amber, I want to see it as amber now based on each distinct invoice ID. If it was good, then amber and now red, see it as red.

I've tried the below formula to create a new column, however I don't know how to integrate this one or other formula in order to include the Distinct of Invoice ID

IF(AND(CONTAINS(Sheet1, Sheet1[Outstanding invoice],"good"), CONTAINS(Sheet1, Sheet1[Outstanding invoice],"amber")), "amber",

IF(AND(CONTAINS(Sheet1, Sheet1[Outstanding invocie],"amber"), CONTAINS(Sheet1, Sheet1[Outstanding invoice],"red")), "red", Sheet1[Outstanding invoice]))

[v-rongtiep-msft]

Hi @Anonymous ,

I have created a simple sample , please refer to my pbix file to see if it helps you.

Create a column first.

weeknum = 
          WEEKNUM('Table'[Reprt Date],1)

Then create 2 measures.

Measure =
VAR _1 =
    DATEDIFF ( MAX ( 'Table'[Invoice Creaated] ), MAX ( 'Table'[Reprt Date] ), DAY )
VAR _2 =
    IF (
        _1 <= 3
            && MAX ( 'Table'[status] ) = "Open",
        "Good",
        IF (
            _1 <= 9
                && _1 >= 4
                && MAX ( 'Table'[status] ) = "Open",
            "Amber",
            IF ( MAX ( 'Table'[status] ) = "Open" && _1 > 9, "Red", BLANK () )
        )
    )
RETURN
    SWITCH ( TRUE (), _2 = "Good", 1, _2 = "Amber", 2, _2 = "Red", 3, BLANK () )

result =
VAR _1 =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[weeknum] = SELECTEDVALUE ( 'Table'[weeknum] )
                && 'Table'[invoice ID] = SELECTEDVALUE ( 'Table'[invoice ID] )
        ),
        [Measure]
    )
RETURN
    IF ( _1 = 1, "Good", IF ( _1 = 2, "Amber", IF ( _1 = 3, "Red", BLANK () ) ) )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Anonymous]

Hi,

Thanks for taking you time and for your reply.

Unfortunately this didn't solve the issue from multiple considerents. 

1 - I will need to take in consideration only working days when deciding if between 1-4 days, 5-10 etc.

2 - I can't use the measure you've created as I will need this to be in a cluster column chart. What I'm trying to achieve is something like below (FYI, below are multiple invoice references, for a specific agent)

The issue with the above, is that Invoice ID0123 for example, was good but now is amber and it still counts as 1 for both of them. If I filter the cluster by that invoice, I get the below, when I would like to have only 1 cluster, saying 1 as Amber (if filtered for ID0123)

I've attached screenshots with raw data and PBI as it doesn't let me attaching them here