DAX Measures with Two Different Date Conditions

[kartiklal7]

Hi All, 

I need some help with a couple of dax measures. 

I have an Opportunities table with the fields in the screenshot below.

I have an active relationship between the Date field in my Calendar table and the ResolutionDate in the Opportunities Table. I also have an inactive relationship between my Date field in Calendar and the ExistingExpiryDate in the Opportunities Table. 

In my report page I also have Year and Quarter slicers.

I need two dax measures with the following conditions

Measure A: 

Calculate AnnualRenewalAmount when Sales Stage = "Closed Sales", ExistingExpiryDate is in selected quarter and resolutiondate is in the selected or prior to the selected quarter. ResolutionDate also cannot be blank or in the future. 

Measure B:

Calculate AnnualRenewalAmount when Sales Stage = "Closed Sales". If the ExistingExpiryDate is prior to the selected quarter, then resolutiondate will be in the selected quarter. If the ExistingExpiryDate is in the selected quarter, then ResolutionDate shall be in the selected or prior to the selected quarter. ResolutionDate cannot be in the future. 

[johnt75 · Accepted solution]

Measure A =
VAR maxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Annual Renewal Amount] ),
        USERELATIONSHIP ( 'Date'[Date], 'Table'[Existing Expiry Date] ),
        'Table'[Sales Stage] = "Closed sales",
        'Table'[Resolution Date] <= maxDate
            && NOT ( ISBLANK ( 'Table'[Resolution Date] ) )
    )

Measure B =
VAR maxDate =
    MAX ( 'Date'[Date] )
VAR minDate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Annual Renewal Amount] ),
        REMOVEFILTERS ( 'Date' ),
        'Table'[Sales Stage] = "Closed sales",
        ( 'Table'[Existing Expiry Date] < minDate
            && 'Table'[Resolution Date] >= minDate
            && 'Table'[Resolution Date] <= maxDate )
            || (
                'Table'[Existing Expiry Date] >= minDate
                    && 'Table'[Existing Expiry Date] <= maxDate
                    && 'Table'[Resolution Date] <= maxDate
                    && NOT ( ISBLANK ( 'Table'[Resolution Date] ) )
            )
    )

[kartiklal7]

Hi @johnt75 ,

Just wondering about the DAX for Measure A - Since the resolutiondate can be in the selected or prior to the selected quarter and if I have selected 2022 Q1 in my date slicer,  will the calculation for this formula also consider resolutiondates prior to 2022 Q1? 

Thanks again!

[johnt75]

I think it will, I think that the USERELATIONSHIP will deactivate the existing relationship from date table to resolution date but you will need to check to make sure