Display accumulative actual and projected values on a continuous line in a chart

[v-lili6-msft]

Scenario:    

In some reports, you may want to display the growing accumulative actual values and projected values in a line chart. You can always show them respectively with two lines, but this time you want to combine them into the same line with solid part for actual values and dashed part for projected values. Then you are able to see the growing trend on the same line just like the following chart.

Now I will describe how to realize this goal with some sample data.

Sample Data: 

In this model, I have the following three tables. Among them, the ‘Actual’ and ‘Projected’ tables are linked to ‘Calendar’ table by Date column.

1. ‘Actual’: contains Dates and Daily Actual Cost;
2. ‘Projected’: contains Dates and Daily Projected Cost;
3. ‘Calendar’: contains Dates between the first date of ‘Actual’ table and the last date of ‘Projected’ table.

Operations：

Before we start, there are several points to note:

1. To display the actual and projected parts separately in solid line and dashed line, actually we still need to use two measures to format separately.
2. To obtain a continuous line without a break, the starting point of the projected line should be the ending point of the actual line.

Now create measures:

Accumulate Actual =

CALCULATE (

    SUM ( 'Actual'[Actual Cost] ),

    FILTER (

        ALL ( 'Calendar'[Date] ),

        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )

    )

)

Max Actual Date =

MAXX ( ALL ( Actual[Date] ), Actual[Date] )

Actual Display =

IF (

    MAX ( 'Calendar'[Date] ) <= [Max Actual Date],

    [Accumulate Actual],

    BLANK ()

)

Accumulate Projected =

CALCULATE (

    SUM ( Projected[Projected Cost] ),

    FILTER (

        ALL ( 'Calendar'[Date] ),

        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )

    )

)

Projected Display =

VAR latestActualDate = [Max Actual Date]

RETURN

    IF (

        MAX ( 'Calendar'[Date] ) = latestActualDate,

        [Accumulate Actual],

        IF (

            MAX ( 'Calendar'[Date] ) > latestActualDate,

            [Accumulate Actual]

                + CALCULATE (

                    SUM ( Projected[Projected Cost] ),

                    FILTER (

                        ALL ( 'Calendar'[Date] ),

                        'Calendar'[Date] > latestActualDate

                            && 'Calendar'[Date] <= MAX ( Projected[Date] )

                    )

                ),

            BLANK ()

        )

)

We can place the measures into a table visual to check whether the results are calculated correctly.

In this table, measures Actual Display and Projected Display are what we need, so we create a line chart with them as Values. Put ‘Calendar’[Date] as Axis.

At last, we need to format the line chart visual. Select the line chart visual and go to Format pane:

1. Data colors: select the same color for both values. You can also set different colors.
2. Shapes: turn on Customize series, expand the dropdown box and change the Line style to Dashed for Projected Display.

You can do some other formatting operations as you like. Now you will get a line chart as shown in Scenario part above.

Extension:

Now that you have one line for the total accumulative actual and projected values in a line chart, you may think what if I want to show lines for different regions separately at the same time? For example, if I have data like below image and I want to show two such lines for Canada and Australia in a chart to compare their difference.

As we know, displaying multiple fields as values with a legend field is currently not supported in a line chart. Because we always need to use two measures to display the actual part and projected part, we cannot put Region as a legend. Instead, we create measures for two regions separately and put all of them in a line chart as values.

Create measures:

Actual Display Australia =

IF (

    MAX ( 'Calendar'[Date] ) <= [Max Actual Date],

    CALCULATE ( [Accumulate Actual], Regions[Region] = "Australia" ),

    BLANK ()

)

Projected Display Australia =

VAR latestActualDate = [Max Actual Date]

RETURN

    IF (

        MAX ( 'Calendar'[Date] ) = latestActualDate,

        CALCULATE ( [Accumulate Actual], Regions[Region] = "Australia" ),

        IF (

            MAX ( 'Calendar'[Date] ) > latestActualDate,

            CALCULATE (

                [Accumulate Actual] + SUM ( Projected[Projected Cost] ),

                FILTER (

                    ALL ( 'Calendar'[Date] ),

                    'Calendar'[Date] > latestActualDate

                        && 'Calendar'[Date] <= MAX ( Projected[Date] )

                ),

                Regions[Region] = "Australia"

            ),

            BLANK ()

        )

    )

Repeat the measures for Canada with the filter Regions[Region] changed to “Canada”. Put them in the chart and format them as mentioned in above context. Now you will get a continuous line for each region in the same chart.

For more details, please refer to the attached PBIX file.

Author: Jing Zhang

Reviewer: Kerry & Ula

Display accumulative actual and projected values on a continuous line.pbix

[Anonymous]

Hi @v-lili6-msft 

Is Is there a way to reset the Actual to current year data and not bring the total from Last Year?  The same goes for Projected.  I want last year to display last year totals and current year to display current year totals.