Forum Discussion

Amitkr174's avatar
Amitkr174
Icon for Helper III rankHelper III
3 years ago
Solved

Calculation based on Next Higher date along with a Condition

Hi - need your help on the below isssue.

Condition is - if Createddate>CaseCompdate,then Createddate-CaseCompdate,else NULL.

It should stop once Createddate>CaseCompdate is true, rest of the lines in the column should be blank. Output sample is attached.

Sample data with Output column

Opp_DealR CaseNumber CreatedDate Case_Comp_dateoldvalueNewvalueOutput
DR3622342 2197519 3/8/2023 2/26/202302 - Prospect05 - Solution Definition and Validation10
DR3622342 2197519 5/25/2023 2/26/202305 - Solution Definition and Validation06 - Customer Commit0
DR3622342 2197519 5/26/2023 2/26/202306 - Customer CommitClosed - Booked0
DR3622342 2197519 5/26/2023 2/26/2023Closed - Booked07 - Execute to Close0
DR3622342 2197519 6/1/2023 2/26/202307 - Execute to CloseClosed - Booked0
DR3630255 2248914 3/20/2023 5/1/202301 - Pre Call Plan02 - Prospect0
DR3630255 2248914 3/28/2023 5/1/202302 - Prospect03 - Opportunity Qualification0
DR3630255 2248914 4/18/2023 5/1/202303 - Opportunity Qualification04 - Circle of Influence0
DR3630255 2248914 5/18/2023 5/1/202304 - Circle of Influence05 - Solution Definition and Validation17

 

Need Help
  • rbriga's avatar
    rbriga
    3 years ago

    This different solution worked for me:

    Output = 
VAR _Earliest =
    CALCULATE (
        MIN ( 'iPOV -MicroConversion'[CreatedDate] ),
        ALLEXCEPT('iPOV -MicroConversion','iPOV -MicroConversion'[CaseNumber]),
        KEEPFILTERS ( 'iPOV -MicroConversion'[CreatedDate] > 'iPOV -MicroConversion'[Case_Comp_date] )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'iPOV -MicroConversion'[CreatedDate] ) = _Earliest,
        DATEDIFF (
            SELECTEDVALUE ( 'iPOV -MicroConversion'[Case_Comp_date] ),
            SELECTEDVALUE ( 'iPOV -MicroConversion'[CreatedDate] ),
            DAY
        ),
        0
    )

6 Replies

  • rbriga's avatar
    rbriga
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    Let's try SQLBI's suggestions for RANK().

    VAR SourceTable =
    ADDCOLUMNS ( 
    CALCULATETABLE(
    	ALLEXCEPT(Table, Table[Opp_DealR], Table[CaseNumber] ),
    	KEEPFILTERS(Table[CreatedDate] > Table[Case_Comp_date])
    	),
    	"@Days", CreatedDate-Case_Comp_date )
VAR Result =
    RANK (
        DENSE,
        SourceTable,
        ORDERBY ( [@Days], DESC, Table[CaseNumber], ASC )
    )
RETURN
    IF(
    	Result =1, 
    	MAX(Table[CreatedDate])- MAX(Table[Case_Comp_date]),
    	BLANK()
    	)

    May need a few adjusments, as I wasn't working on actual tables.

      • rbriga's avatar
        rbriga
        Icon for Impactful Individual rankImpactful Individual
        3 years ago

        This different solution worked for me:

        Output = 
VAR _Earliest =
    CALCULATE (
        MIN ( 'iPOV -MicroConversion'[CreatedDate] ),
        ALLEXCEPT('iPOV -MicroConversion','iPOV -MicroConversion'[CaseNumber]),
        KEEPFILTERS ( 'iPOV -MicroConversion'[CreatedDate] > 'iPOV -MicroConversion'[Case_Comp_date] )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'iPOV -MicroConversion'[CreatedDate] ) = _Earliest,
        DATEDIFF (
            SELECTEDVALUE ( 'iPOV -MicroConversion'[Case_Comp_date] ),
            SELECTEDVALUE ( 'iPOV -MicroConversion'[CreatedDate] ),
            DAY
        ),
        0
    )