Forum Discussion
Adding Trendline to a Line Graph While Showing Fiscal Year & Quarters
Thank you for the sample data. Which trend methodology do you want to apply? Sliding window? how wide?
Here is an example of a "five wide" window.
Or do you want to find the trend based on all data points?
Hi RPeruski ,
To add a proper overall trendline in your visual, while keeping your Fiscal Year / Quarter / Month formatting, you need to retain a continuous axis for LINESTX to work correctly, but custom-format the display of the X-axis.
We can do a few workarounds:
1.Make sure your 'Calendar'[Date] column is marked as the primary date column (it should be of type Date and continuous).
2.We need to use LINESTX properly:
TotalTrend =
VAR a =
SUMMARIZE(
ALLSELECTED('Calendar'),
'Calendar'[Date],
"ct", [ct]
)
VAR b =
EVALUATEANDLOG(
LINESTX(a, [ct], [Date])
)
RETURN
CALCULATE(
b,
KEEPFILTERS('Calendar'[Date])
)You may also use 'Calendar'[YearMonth] if you have a combined column, but 'Date' ensures continuity.
3.Instead of breaking the X-axis continuity for FY/Quarter/Month display, use a custom tooltip or dynamic label:
Create a new calculated column in your date table:
X-Axis Label = 'Calendar'[Month] & " " & 'Calendar'[FiscalQuarter] & " " & 'Calendar'[FY]
Use this for tooltips or a slicer, but do not use it for the axis. Keep the actual X-axis as a continuous date.
4.In the visual formatting pane:
- Go to X-axis > Type > Continuous
- Optionally, hide the axis labels if the formatting is messy, and use tooltips to convey the fiscal context.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Super UserThank you for the sample data. Which trend methodology do you want to apply? Sliding window? how wide?
Here is an example of a "five wide" window.
Or do you want to find the trend based on all data points?
Mainly focusing on a trendline from all data points. But this window function would be good for other projects I have too.
- lbendlin
Your sample data has no real slope, so the trend line will coincide with the average line.
I agree that the data set does not have much of a slope but I would still like to identify a straight line estimating the overall slope of all the data, eventually we want to see the occurance increasing over time and the line sloping negative.
Hi RPeruski ,
To add a proper overall trendline in your visual, while keeping your Fiscal Year / Quarter / Month formatting, you need to retain a continuous axis for LINESTX to work correctly, but custom-format the display of the X-axis.
We can do a few workarounds:
1.Make sure your 'Calendar'[Date] column is marked as the primary date column (it should be of type Date and continuous).
2.We need to use LINESTX properly:
TotalTrend =
VAR a =
SUMMARIZE(
ALLSELECTED('Calendar'),
'Calendar'[Date],
"ct", [ct]
)
VAR b =
EVALUATEANDLOG(
LINESTX(a, [ct], [Date])
)
RETURN
CALCULATE(
b,
KEEPFILTERS('Calendar'[Date])
)You may also use 'Calendar'[YearMonth] if you have a combined column, but 'Date' ensures continuity.
3.Instead of breaking the X-axis continuity for FY/Quarter/Month display, use a custom tooltip or dynamic label:
Create a new calculated column in your date table:
X-Axis Label = 'Calendar'[Month] & " " & 'Calendar'[FiscalQuarter] & " " & 'Calendar'[FY]
Use this for tooltips or a slicer, but do not use it for the axis. Keep the actual X-axis as a continuous date.
4.In the visual formatting pane:
- Go to X-axis > Type > Continuous
- Optionally, hide the axis labels if the formatting is messy, and use tooltips to convey the fiscal context.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy