Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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:
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?
Solved! Go to Solution.
@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"
)
@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"
)
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.