Forum Discussion
Helper IIICumulative Sales lines
Helper IIIHi BeaBF,
Thank you for your quick reaction.
I have tried the formulas you explained but the results are not as expected. As you can see below the lines are just one straight horizontal line. It is the total sales for all months.
Do you know what is not correct here?
Super UserBased on the screenshot you provided, it looks like the issue is with the date filter on the chart. The measures I provided calculate the cumulative sales for each fiscal year up to the current month, but the chart is displaying data for all months in the selected date range.
To fix this issue, you can modify the chart to use the 'Calendar Posting Date'[MonthOffset] field on the x-axis instead of 'Calendar Posting Date'[Month]. This will allow the chart to correctly display the cumulative sales for each fiscal year up to the selected month.
Here are the updated measures:
Current Year Sales:
Current Year Sales =
VAR CurrentYear = SELECTEDVALUE('Calendar Posting Date'[CurFiscalYearOffset])
VAR CurrentMonth = SELECTEDVALUE('Calendar Posting Date'[CurMonthOffset])
RETURN
CALCULATE(
SUM('Append3'[EUR Sales]),
FILTER(
ALL('Calendar Posting Date'),
'Calendar Posting Date'[CurFiscalYearOffset] = CurrentYear &&
'Calendar Posting Date'[MonthOffset] <= CurrentMonth
)
)
Last Year Sales:
Last Year Sales =
VAR LastYear = SELECTEDVALUE('Calendar Posting Date'[CurFiscalYearOffset]) - 1
RETURN
CALCULATE(
SUM('Append3'[EUR Sales]),
FILTER(
ALL('Calendar Posting Date'),
'Calendar Posting Date'[CurFiscalYearOffset] = LastYear
)
)
Two Years Ago Sales:
Two Years Ago Sales =
VAR TwoYearsAgo = SELECTEDVALUE('Calendar Posting Date'[CurFiscalYearOffset]) - 2
RETURN
CALCULATE(
SUM('Append3'[EUR Sales]),
FILTER(
ALL('Calendar Posting Date'),
'Calendar Posting Date'[CurFiscalYearOffset] = TwoYearsAgo
)
)
Current Year Sales YTD:
Current Year Sales YTD =
VAR CurrentYear = SELECTEDVALUE('Calendar Posting Date'[CurFiscalYearOffset])
VAR CurrentMonth = SELECTEDVALUE('Calendar Posting Date'[CurMonthOffset])
RETURN
CALCULATE(
[Current Year Sales],
FILTER(
ALL('Calendar Posting Date'),
'Calendar Posting Date'[CurFiscalYearOffset] = CurrentYear &&
'Calendar Posting Date'[MonthOffset] <= CurrentMonth
)
)
Here is the modified chart setup:
- Add 'Calendar Posting Date'[MonthOffset] to the x-axis.
- Add [Current Year Sales], [Last Year Sales], and [Two Years Ago Sales] to the values area of the chart.
- Apply a visual level filter to only show data for months up to and including the selected month (use the 'Calendar Posting Date'[MonthOffset] field to filter the data).
- Add a new series to the chart for the [Current Year Sales YTD] measure, and set the line style to a dotted line to indicate that the data is incomplete for the current fiscal year.
BBF
- JC2022
Hi BeaBF,
If I use your Current year sales measure and put [CurMonthOffset] on the x-axis it will show the line graph below. This way I do not have the month names on the x-axis, which is not as it should. I would like to see all month names (all 12) on the x-axis, starting with October (start fiscal year). So not as it is now with all [CurMonthOffset].
Please see my first post for the requested end result graph.
- BeaBF
To display all 12 months on the x-axis starting with October, you can create a custom calendar table with a column for each month in the fiscal year. You can then join this custom calendar table with your sales data using the fiscal year and month offset columns.
BBF
- JC2022
but the Current Year Sales still shows more than the current year right? For example there is a value for [CurMonthOffset] = -20. Plus it shows a value for [CurMonthOffset] = 1 while the line should stop at 0 (and the line between -1 and 0 is then dotted)
By the way is it something strange I am trying to reach as an endresult? What is a more convenient manner to visualize this?