Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
jaylyn
Helper I
Helper I

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

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... 

 

 

 36590reten pic example sample data.JPG

4 REPLIES 4
jaylyn
Helper I
Helper I

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

v-junyant-msft
Community Support
Community Support

Hi @jaylyn ,

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

vjunyantmsft_0-1707799980275.png

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.

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.

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? MicrosoftTeams-image.pngMicrosoftTeams-image (3).pngMicrosoftTeams-image (4).pngMicrosoftTeams-image (2).pngMicrosoftTeams-image (1).png

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.