Sum of Effort between 2 dates based on first instance of a condition

[Anonymous]

Example Table 

Looking for suggestions on how to approach this the best way.  I need to be able to show total of Worked, summarized by consumer number, using the Placement date as the start date, and the Date of the first instance of Action code "pmtschsm".

 In the attached sample table example, we would expect the totals for each consumer to be:

consumer 6297225 - 19

consumer 6295290 - 14

Any advice on how to approach or solve this problem would be greatly appreciated.  Thanks all

[Ashish_Mathur]

Hi,

Access Denied message when i try to download the file.  For 6297225 why should the answer be 19?  What do you mean by Date of the first instance of Action code "pmtschsm"?  In the MS Excel file that you upload, please show the expected result with simple Excel formulas so that your logic can be understood

[Anonymous]

@Ashish_Mathur sorry Ashish, not sure why the link is blocked.  I will check on it.  But it's ok @DataInsights have solved the problem.  Thank you so much for responding!

[DataInsights · Accepted solution]

@Anonymous,

Try this measure:

Total Worked = 
VAR vPlacementDate =
    CALCULATE (
        MAX ( Table1[Placement] ),
        ALLEXCEPT ( Table1, Table1[ConsumerNumber] )
    )
VAR vMinDatePmt =
    CALCULATE (
        MIN ( Table1[Date] ),
        ALLEXCEPT ( Table1, Table1[ConsumerNumber] ),
        Table1[ActionCode] = "PMTSCHSM"
    )
VAR vResult =
    CALCULATE (
        SUM ( Table1[Worked] ),
        ALLEXCEPT ( Table1, Table1[ConsumerNumber] ),
        Table1[Date] >= vPlacementDate,
        Table1[Date] <= vMinDatePmt
    )
RETURN
    vResult

[Anonymous]

@DataInsights This worked PERFECTLY!  Amazing job.  Thank you so much for responding!