cancel
Showing results for
Did you mean:
Helper I

## Show average values on a chart with fixed X axis

I have the following dataset:

 PersonId FirstLetterReceivedDate LetterReceivedDate LetterStopDate Letters Y/N 1 01-01-2015 01-01-2015 01-01-2018 1 Yes 1 01-01-2015 01-01-2016 01-01-2018 2 Yes 1 01-01-2015 05-03-2017 01-01-2018 3 No 2 01-12-2017 01-12-2017 05-05-2018 1 No 2 01-12-2017 03-12-2017 05-05-2018 4 No 2 01-12-2017 04-12-2018 05-05-2018 5 Yes 3 01-12-2018 01-12-2018 25-06-2020 1 No 3 01-12-2018 01-12-2019 25-06-2020 6 Yes 3 01-12-2018 25-12-2019 25-06-2020 8 No

The dataset shows 3 different personIds and..:

- when they should stop receiving letters (LetterStopDate)

What i want to do:

Problem 1)

With a slicer filter that uses FirstLetterReceivedDate, I want to show on a line chart the average number of letters received per personId since FirstLetterReceivedDate. 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 received 2 letters in year 0-1 after their FirstLetterReceivedDate, and 2 letters from year 0-2.

- PersonId 2 received 4 letters in year 0-1 after their FirstLetterReceivedDate, and 5 letters from year 0-2.

- PersonId 3 received 6 letters in year 0-1 after their FirstLetterReceivedDate, and 8 letters from year 0-2.

So, the average for year 0-1 is 4 letters, and the average for year 0-2 is 5 letters. year 3 would be 0 letters.

Problem 2):

If the date period selected from the slicer is only 1 year (for example 2016-01-01 to 2017-01-01), then the line chart should should drill down on months 0-12 instead of years 1-4.

How to accomplish this complexity?

1 ACCEPTED SOLUTION
Super User

@msuser48 , You can create an new column like this and use

Year Diff =
return
Switch(True(),
_diff <=1, " 0- 1",
_diff <=2, " 1- 2",
_diff <=3, " 3- 4",
"GT 3"
)

Super User

@msuser48 , You can create an new column like this and use

Year Diff =
return
Switch(True(),
_diff <=1, " 0- 1",
_diff <=2, " 1- 2",
_diff <=3, " 3- 4",
"GT 3"
)

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors