I need to get a value from previous x amount of days from max date

[jaylyn]

I have a measure that calculates rolling365 annual retention. I need another calculation that gives me the value on the chart that is 90 days prior to that date i'm looking at on the chart.

I have links attached to the same data and instructions and pbix file and the original measures that calulates the 365 retention. Thanks for your help.

https://drive.google.com/file/d/11PltldOw7Zhe-t8HnwExyAld8jSkuBJ8/view?usp=sharing https://docs.google.com/spreadsheets/d/1-Uoki_YzW5gVDILExmUaPV9oXGJOONy7/edit?usp=drive_link&ouid=11... 

 

[jaylyn]

Thank you, Dino. I will try this, i'm not sure why the link doesn't work. Thanks again!

[Anonymous]

Hi @jaylyn ,

Sorry but the link you provide can't be reached:

But I can give you a sample:

Retention 90 Days Ago = CALCULATE([Your365RetentionMeasure], DATEADD('YourDateTable'[Date], -90, DAY))

 Replace Your365RetentionMeasure with the name of your existing measure that calculates the rolling 365 retention, and 'YourDateTable'[Date] with the name of your date column.
This measure uses the CALCULATE function to modify the filter context of your existing retention measure to 90 days in the past using the DATEADD function.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jaylyn]

eventually I want another field that gives me the average of the last 3 months retention (so december 2023, jan 2024, feb 2024) but the calculation for the 90 day value has to be matching all the time before i can get the total retention for the 3 months and then the average of the 3 months retention.

[jaylyn]

Hi, Dino. That method works mostly, but on some max-date, the value is off slightly by .1 or .2 or up to .4% difference, but on some max-dates, it matches 100%. Do you know what could be the issue?