Show average values on a chart using fixed x axis

[msuser48]

I have the following dataset:

PersonId	FirstLetterReceivedDate	LetterReceivedDate	LetterStopDate
1	01-01-2015	01-01-2015	01-01-2016
1	01-01-2015	01-02-2015	01-01-2016
1	01-01-2015	05-03-2015	01-01-2016
2	01-12-2017	01-12-2017	01-05-2018
2	01-12-2017	03-12-2017	01-05-2018
2	01-12-2017	04-12-2018	01-05-2018
3	01-12-2018	01-12-2018	30-12-2020
3	01-12-2018	01-12-2019	30-12-2020
3	01-12-2018	25-12-2019	30-12-2020
4	01-01-2021	01-01-2021	01-01-2023

The dataset shows 3 different personIds and..:

- when they received their first letter (FirstLetterReceivedDate)

- when they received each letter (LetterReceivedDate)

- when they should stop receiving letters (LetterStopDate)

What I want to do:

With a slicer filter that uses FirstLetterReceivedDate, I want to show on a line chart when people on average stop receiving letters. The line chart's X axis should be fixed to 4 years, so it looks something like this:

Example:

If i set the date slicer to dates between for example 01-01-2015 and 01-12-2018, we know that..:

- PersonId 1 stopped receiving letters 1 year after FirstLetterReceivedDate

- PersonId 2 stopped receiving letters 1 year and 6 months after FirstLetterReceivedDate, so we still count this as 1 year (it has to be a full 2 years if we count is as 2 years).

- PersonId 3 stopped receiving letters 2 years and 1 month after FirstLetterReceivedDate, so we count this as 2 years

- PersonId 4's FirstLetterReceivedDate is outside slider range, so we do not include this person.

So the chart would look like this:

How to accomplish this?

[amitchandak]

@msuser48 ,

Create a measure = averagex(Table, datediff([First Date], [Stop Date], month)/12)

and use that with first date of last date on axis

You can also have a common date table

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...