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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.