Cumulative Sales lines

[JC2022]

Hi,

The line graph below is my goal.

I would like to create a cumulative sales line for the current year (blue line), the current year -1 (green line) and the current year -2 (red line). All the lines should start at 0 sales in October. The lines for the completed years (green and red lines) should be shown complete, for all 12 months. The current year (blue line) should show a line for all completed months, so in this case till February. With a dotted line (blue) for the current month, this so it is clear month March is not finished yet.

The tables are like this (see below). Related on [Date] field on both tables. The 'Append3'[EUR Sales] should be summed (cumulative). In the 'Calendar Posting Date' table there is the [CurFiscalYearOffset] and [CurMonthOffset].

How do I create the measures to reach my goal (see line graph below)?

[BeaBF]

@JC2022 Hi!

To achieve your goal, you can use the following measures in your Power BI report:

Current Year Sales: This measure calculates the cumulative sales for the current fiscal year.
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: This measure calculates the cumulative sales for the previous fiscal year.
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: This measure calculates the cumulative sales for two fiscal years ago.
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: This measure calculates the sales for the current fiscal year up to the current month.
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
)
)
You can then create a line chart with 'Calendar Posting Date'[Month] on the x-axis and the following measures on the y-axis:

[Current Year Sales]
[Last Year Sales]
[Two Years Ago Sales]
[Current Year Sales YTD]
To display a dotted line for the current month, you can create a calculated column in the 'Calendar Posting Date' table as follows:

Current Month Indicator = IF(MAX('Calendar Posting Date'[MonthOffset]) = SELECTEDVALUE('Calendar Posting Date'[CurMonthOffset]), 1, 0)
Then, in the line chart, you can add a new series for the 'Current Month Indicator' column and set the line style to dotted. This will display a dotted line for the current month.

BBF

[JC2022]

Hi @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?

[BeaBF]

@JC2022 

Based 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]

@JC2022 

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]

@BeaBF 

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?