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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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"
)

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"
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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