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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jaylyn
Frequent Visitor

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
Frequent Visitor

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.