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 UserYour 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
Hi RPeruski ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
B Manikanteswara Reddy
Hi RPeruski ,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Please don't forget to give a "Kudos " – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy