Measure filter to select 2 statuses, then select newest status

[Anonymous]

I have a status history table that shows the various statuses an opportunity has transistioned through and the date it first hit that status.  What I am trying to do is create a measure that finds every deal that hit 2 of the statuses then selects the newest of the two.  I first created the measure with an or function to select the two statuses and hoped it would select the lastest, but that was wishful thinking.  I then tried to add a max date function but it seems to be eliminating opps that have transitioned beyond the two statuses.  Here is the function I've got so far....

Pipeline Total Volume 2 = CALCULATE(SUM('Status History'[Finance Amount]), 'Status History'[Application Status] = "Funding Review" || 'Status History'[Application Status] = "Documents Received" && 'Status History'[Status Date] = Max ('Status History'[Status Date])

The funding review status comes after documents received.  Not all opps have transistioned to funding review, and not all do.

[AlexisOlson · Accepted solution]

If you don't have duplicated Finance Amount rows in your table, then you can just write

CALCULATE (
    SUM ( 'Status History'[Finance Amount] ),
    'Status History'[Application Status] IN { "Funding Review", "Documents Received" }
)

If your data isn't set up so that Finance Amount is additive (where the above works), then you'll need to provide some example data and desired result since the correct measure heavily depends on how the data is set up.

[AlexisOlson]

Logically, A || B && C = A || ( B && C ) which is not the same as ( A || B ) && C, so you might want some parentheses around your || combination.

Beyond that though, it's not quite clear to me if the deal must hit both statuses or not to be counted and whether you want your measure to return and an amount, a date, or a status.

[Anonymous]

I want the measure to return a sum of all the finance amounts for deals that are in either docs received or funding review, and if the opp is in both I only want to count the finance amount once for the sum.  

[AlexisOlson · Accepted solution]

If you don't have duplicated Finance Amount rows in your table, then you can just write

CALCULATE (
    SUM ( 'Status History'[Finance Amount] ),
    'Status History'[Application Status] IN { "Funding Review", "Documents Received" }
)

If your data isn't set up so that Finance Amount is additive (where the above works), then you'll need to provide some example data and desired result since the correct measure heavily depends on how the data is set up.

[Anonymous]

Here is how the data is structure

Application Number	Application Status	Status Date	Funding Analyst	Finance Amount
10993	Documents Received	9/21/2021 0:00		$55,000
10993	Funding Review	9/21/2021 0:00		$55,000
22824	Documents Received	9/16/2021 0:00		$164,890
22824	Funding Review	9/16/2021 0:00		$164,890
25970	Documents Received	9/20/2021 0:00		$198,481
25970	Funding Review	9/20/2021 0:00		$198,481
29009	Documents Received	9/27/2021 0:00		$91,923
29009	Funding Review	9/27/2021 0:00		$91,923
30541	Funding Review	9/20/2021 0:00		$425,000
30715	Documents Received	9/2/2021 0:00		$1,347,800
30715	Funding Review	9/21/2021 0:00		$1,347,800
32267	Documents Received	9/3/2021 0:00		$6,251
32267	Funding Review	9/3/2021 0:00		$6,251
32412	Documents Received	9/1/2021 0:00		$38,000
32412	Funding Review	9/2/2021 0:00		$38,000
32452	Documents Received	9/27/2021 0:00		$159,375
32452	Funding Review	9/27/2021 0:00		$159,375
32476	Documents Received	9/29/2021 0:00		$180,000
32615	Documents Received	9/3/2021 0:00		$74,264
32615	Funding Review	9/3/2021 0:00		$74,264
32729	Documents Received	9/14/2021 0:00		$403,500
32729	Funding Review	9/27/2021 0:00		$403,500
32944	Documents Received	9/10/2021 0:00		$10,000
32944	Funding Review	9/10/2021 0:00		$10,000
33006	Documents Received	9/30/2021 0:00		$650,000
33081	Documents Received	9/16/2021 0:00		$241,000
33327	Documents Received	9/30/2021 0:00		$20,000
33427	Funding Review	9/22/2021 0:00		$2,947,492

There are more columns but those are the ones relevant to this measure.  The status order would be docs received, then funding review. The time isn't currently captured as part of the stage transisions, but I do have a table that has the status orders as...

The goal would be to only count the finance amount of apps once in the sum measure that appear in both statuses.

[AlexisOlson · Accepted solution]

Got it. I'd recommend adding a calculated column on the table

IsLastStatus =
VAR MaxDate =
    CALCULATE (
        MAX ( 'Status History'[Status Date] ),
        ALLEXCEPT ( 'Status History', 'Status History'[Application Number] )
    )
VAR LastStatus =
    CALCULATE (
        MAX ( 'Status History'[Application Status] ),
        ALLEXCEPT ( 'Status History', 'Status History'[Application Number] ),
        'Status History'[Status Date] = MaxDate
    )
RETURN
    'Status History'[Application Status] = LastStatus

If you have this, then the measure I suggest only needs a small modification.

CALCULATE (
    SUM ( 'Status History'[Finance Amount] ),
    'Status History'[Application Status] IN { "Funding Review", "Documents Received" },
    'Status History'[IsLastStatus]
)

(Since [IsLastStatus] is a boolean it can function without specifying = TRUE. )

[Anonymous]

Is there a way to modify that so it picks the last status between those two, because there are other statuses after the two (documents received, and Funding Review).  Like excluding funded?

[AlexisOlson]

Sure. Add the same filter to both CALCULATE functions in [IsLastStatus].

'Status History'[Application Status] IN { "Funding Review", "Documents Received" }

[Anonymous]

Cool, I'll try this tomorrow.