Can this DAX measure for calculating relative dates be simplified?

[Anonymous]

Below is some DAX code I've written to calucalte a relative date. The user selects a date from a ReferenceDate calendar, this is separate from all other tables. [Max Quarter] calculates the reference date we're looking at. [IRR Date] then calculated relative dates based on the [Max Quarter] and a table called 'Trailing IRR' (screen shot attached). Have I over complicated the [IRR Date] measure?? Is there anything I can do to improve these measures? Let me know if I haven't explained it well. 

Max Quarter = 
SELECTEDVALUE(
    ReferenceDate[Quarter end date],
    CALCULATE(
        MAX('Calendar'[Quarter end date]),
        ALLSELECTED('Calendar')))

IRR Date = 
VAR TrailingDate = 
IF(
    SELECTEDVALUE('Trailing IRR'[Time Period])="3 Month",
    CALCULATE(
        MAX('Calendar'[Date]),
        PREVIOUSQUARTER(
                CALCULATETABLE(
                    VALUES('Calendar'[Date]),
                    FILTER(ALL('Calendar'),
                    'Calendar'[Date]=[Max Quarter])))),
    DATE(
        YEAR([Max Quarter])-SELECTEDVALUE('Trailing IRR'[Year]),
        MONTH([Max Quarter]),
        DAY([Max Quarter])))
    
 RETURN
 IF(
     TrailingDate=[Max Quarter],
     BLANK(),
     TrailingDate)

[PiEye · Accepted solution]

Great! Don't forget to mark it as resolved if it helps, or let me know if it doesn't

Pi

[PiEye]

Hi @Anonymous 

You could take advantage of the "EDATE" function which subtracts a specified number of months from a date.

In my example below, I have added another field to your lookup table which is the number of months difference as implied by the selected time period:

You can use this single selected value as a parameter within the EDATE() function, removing the need for a long if statement:

Relative Date = EDATE([Selected Date],SELECTEDVALUE('year ref'[MonthDiff]))  

Where [Selected Date] is the date you wish to perform comparison on and 'year ref' is the table with the selection value.

HTH

Pi

[Anonymous]

Thank you for this suggestion! I'll give it a go, I definitely think removing the IF statement will help