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

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
msuser48
Helper I
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 received their first letter (FirstLetterReceivedDate)

- when they received each letter (LetterReceivedDate)

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

msuser48_0-1669632050033.png

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
amitchandak
Super User
Super User

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

 

Year Diff =
var _diff = datediff( [FirstLetterReceivedDate],[LetterReceived],month)/12
return
Switch(True(),
_diff <=1, " 0- 1",
_diff <=2, " 1- 2",
_diff <=3, " 3- 4",
"GT 3"
)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

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

 

Year Diff =
var _diff = datediff( [FirstLetterReceivedDate],[LetterReceived],month)/12
return
Switch(True(),
_diff <=1, " 0- 1",
_diff <=2, " 1- 2",
_diff <=3, " 3- 4",
"GT 3"
)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

May 2025 Monthly Update

Fabric Community Update - May 2025

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