In this tutorial I want to showcase a very interesting way of using a Line and stacked column chart that will display Total Sales Last Year, Total Sales for each month of the current selected year and the Total Sales of the year. The end result will look like this:

Usually Stacked column charts or Line charts can display one year or quarter or selected month. The innovation here is that the selected year is surrounded by last year and current year totals.
To accomplish this, we need a supporting table and two measures. For the Column values I need a measure for Last Year and This Year while for the Line for columns I need a measure to show months this year.
Here is where the SWITCH, SELECTEDVALUE and INTERSECT functions come into play. In this tutorial I will focus on using SWITCH and SELECTEDVALUE to create the measure for Last Year and This Year while in the next tutorial I will cover the INTERSECT function.
At the end of this tutorial I will end up with the following chart:

1 Create an Auxiliary Date table
I go to Enter Data and name this table dimDateAux. I need two columns: one for index and one for the intermediate months. I start with id 1 and Last Year then all the months from 2 to 13 and the last row is 14 This Year. I also sort the Int Month column by the Id column.

One important note is that I do not create any connection between this supporting table to the other tables. I place it beneath the fact tables in the Model canvas.

2 Column values using SWITCH statement
I will start by calculating the Total Sales for current year and last year. The Total Sales measure and Sales Last Year are already in the Main Measures table but here I want to show how to calculate Total Sales LY using SAMEPRIODLASTYEAR.
Let’s start by clicking on the New measure button and write Last Year and This Year. Here is a good idea to use variables as they make the measures much cleaner. The first variable is for Total Sales LY so I type in VAR followed by the variable name. vTotalSalesLY. Notice that variables are color coded to green.
Instead of using DATEADD as I did previously, I use SAMEPERIODLASTYEAR and choose the Date column from the Dates table followed by RETURN to close the variable.
Last Year and This Year =
VAR vTotalSalesLY =
CALCULATE(
[Total Sales], SAMEPERIODLASTYEAR(Dates[Date])
)
RETURN
Now I need to insert a SWITCH statement. SWITCH is part of the logical function like IF, IFERROR, AND etc. According to the official documentation, SWITCH “Evaluates an expression against a list of values and returns one of multiple possible result expressions”. The format of SWITCH is the following: (<expression>, <value>, <result>[, <value>, <result>]…[, <else>]).
The result we are after is Total Sales this Year and Last Year so we need to add a filter context. The best filter function in this case is SELECTEDVALUE because it “Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult”.
Its format is SELECTEDVALUE(<columnName>[, <alternateResult>]). This is where I use the supporting table because I want to get the Sales LY then all the months in the current year followed by a total of current year. I start by writing SWITCH( then I choose the column from the dimDateAux followed by a comma. After the comma I write “Last year”.
Now for the value part I write in the variable for Total Sales Last Year followed by a comma and “This Year”. For the result I want Total Sales. The alternate result we can put BLANK and close the argument.
The final formula is:
Last Year and This Year =VAR vTotalSalesLY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( Dates[Date] ) )
RETURN
SWITCH (
SELECTEDVALUE ( dimDateAux[Int Month] ),
“Last Year”, vTotalSalesLY,
“This Year”, [Total Sales],
BLANK ()
)
In the next post I will create the measure for the Line values and get the total sales by month of the current year using the INTERSECT function.
Leave a Review