Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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)
Solved! Go to Solution.
Great! Don't forget to mark it as resolved if it helps, or let me know if it doesn't
Pi
Great! Don't forget to mark it as resolved if it helps, or let me know if it doesn't
Pi
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:
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
User | Count |
---|---|
21 | |
20 | |
12 | |
10 | |
8 |
User | Count |
---|---|
30 | |
28 | |
14 | |
13 | |
11 |