Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kartiklal7
Frequent Visitor

DAX Measures with Two Different Date Conditions

Hi All, 

 

I need some help with a couple of dax measures. 

 

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

kartiklal7_0-1658998264175.png

 

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. 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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] ) )
            )
    )

View solution in original post

3 REPLIES 3
johnt75
Super User
Super User

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] ) )
            )
    )

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!

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.