Forum Discussion

JC2022's avatar
JC2022
Icon for Helper III rankHelper III
3 years ago

Cumulative Sales lines

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)?

 

 

 

 

6 Replies

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      JC2022
      Icon for Helper III rankHelper III
      3 years ago

      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's avatar
        BeaBF
        Icon for Super User rankSuper User
        3 years ago

        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