cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
halomo97
Advocate I
Advocate I

Can this DAX measure for calculating relative dates be simplified?

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. 

 

halomo97_0-1676645278449.png

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)

 

1 ACCEPTED SOLUTION
PiEye
Resolver II
Resolver II

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

 

Pi

View solution in original post

3 REPLIES 3
PiEye
Resolver II
Resolver II

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

 

Pi

PiEye
Resolver II
Resolver II

Hi @halomo97 

 

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:

PiEye_0-1676739728743.png

 

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

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors