Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to calculate a measure which will always give me the most up todate value (the max date) for the NAV divided by the NAV on the a number of date ranges eg the last month, the last 3 months, the last year. I am trying to calculate the % between any of these two periods. The data is set out below. Thanks in advance.
Solved! Go to Solution.
Hi @Absalon29
Here is one approach to consider as a calculated measure. Just replace where I have Table 3 with your own table and set the format to Percent.
Measure 2 = VAR MonthsToLookBack = 3 VAR maxDate = MAX('Table 3'[Date]) VAR otherDate = CALCULATE(MAX('Table 3'[Date]),FILTER('Table 3','Table 3'[Date] < DATEADD('Table 3'[Date],MonthsToLookBack,MONTH))) VAR LatestNAV = CALCULATE(MAX('Table 3'[Nav]),FILTER('Table 3',[Date]=maxDate)) VAR otherNAV = CALCULATE(MAX('Table 3'[Nav]),FILTER('Table 3',[Date]=otherDate)) RETURN DIVIDE (LatestNAV - otherNAV,LatestNAV)
Hi @Absalon29,
Download the file from here.
Hi @Absalon29,
Download the file from here.
Ashish, Thanks so much for you help. I'm getting there but keep hitting dax roadblocks;your input is invaluable and much appreciated. Thanks.
You are most welcome.
Hi Ashish, I am struggling a bit to implement your solution which works for periods like 1 month, 3 months or 12 months but how could I factor in Year to date and since inception calculations using your model.
Hi @Absalon29
Here is one approach to consider as a calculated measure. Just replace where I have Table 3 with your own table and set the format to Percent.
Measure 2 = VAR MonthsToLookBack = 3 VAR maxDate = MAX('Table 3'[Date]) VAR otherDate = CALCULATE(MAX('Table 3'[Date]),FILTER('Table 3','Table 3'[Date] < DATEADD('Table 3'[Date],MonthsToLookBack,MONTH))) VAR LatestNAV = CALCULATE(MAX('Table 3'[Nav]),FILTER('Table 3',[Date]=maxDate)) VAR otherNAV = CALCULATE(MAX('Table 3'[Nav]),FILTER('Table 3',[Date]=otherDate)) RETURN DIVIDE (LatestNAV - otherNAV,LatestNAV)
Hi Phil,
Any chance you can show how I would adjust to fact in year to date and since inception dates.
Finally figured it out! Thanks.
Hi Phil, I copied this over to my model and the measure isn't producing any results. If I have to include Year to date and since inception could you explain what alternations I need to make to your measure
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |