Sometimes a requirement in a project is to have visual that represents a trend over time. Easy. Value, on the axis some time dimension values and voila! Easy as pie.
Well, however, then there’s the client that is/was used to seeing trends and the comparison versus some previous value, that is also not from the same hierarchy level, in the same visual (graph). Therefore, the requirement could be comparing values by the month, but having a year average (previous year) as a comparison to the trend. This would mean having monthly values on the same axis as a year value.
It is not always visually better, but it can be helpful to know how to model/code it. One example could look like this:
In this visualization, we can see a trend of the current year on a monthly basis, as well as the monthly average for the current year (2017 in this case) and the previous year. Interesting, huh?
Let’s see how we can achieve that.
The first thing that we need to create is the mixed values of months and year in a single column. How we can do that you ask? Simple – Calculated table. From the Date dimension get all years and all months and put the “names” in the same column so that we can put the values on the same axis even though they are of “different levels”.
NL Time =
UNION (
SELECTCOLUMNS (
ADDCOLUMNS (
VALUES ( ‘Calendar'[Year] ),
“NL Time”, ‘Calendar'[Year],
“Date”, CALCULATE ( MAX ( ‘Calendar'[Date] ) ),
“DateMin”, CALCULATE ( MIN ( ‘Calendar'[Date] ) ),
“Order”, “0” & RANKX ( VALUES ( ‘Calendar'[Year] ), ‘Calendar'[Year],, ASC, DENSE ),
“Type”, “FY”
),
“NL Time”, [NL Time],
“Date”, [Date],
“DateMin”, [DateMin],
“Order”, [Order],
“Type”, [Type]
),
SELECTCOLUMNS (
ADDCOLUMNS (
VALUES ( ‘Calendar'[MonthShort] ),
“NL Time”, ‘Calendar'[MonthShort],
“Date”, CALCULATE ( MAX ( ‘Calendar'[Date] ) ),
“DateMin”, CALCULATE ( MIN ( ‘Calendar'[Date] ) ),
“Order”, “1”
& RANKX (
ALL ( ‘Calendar’ ),
CALCULATE ( MAX ( ‘Calendar'[MonthNo] ) ),
,
ASC,
DENSE
),
“Type”, “Month”
),
“NL Time”, [NL Time],
“Date”, [Date],
“DateMin”, [DateMin],
“Order”, [Order],
“Type”, [Type]
)
)
And the second thing is to create a custom measure so that it will show calculate the appropriate numbers for all the axis values: for the year values it shows a monthly average of that year, and for the month values it just calculates the monthly value. Of course, taking into account the filters in the application. And we limit showing the previous year-PY monthly average (1 value), the current year-CY monthly average (1 value), and the monthly trend for CY (12 values.. or less).
Revenue – NL Time =
VAR __type =
SELECTEDVALUE ( ‘NL Time'[Type] )
VAR dateMin_ =
SELECTEDVALUE ( ‘NL Time'[DateMin] )
VAR date_ =
SELECTEDVALUE ( ‘NL Time'[Date] )
VAR selDate =
MAX ( ‘Calendar'[Date] )
VAR selDateMinDATE =
DATE ( YEAR ( selDate ), MONTH ( dateMin_ ), DAY ( dateMin_ ) )
VAR selDateDATE =
DATE ( YEAR ( selDate ), MONTH ( date_ ), DAY ( date_ ) )
RETURN
SWITCH (
TRUE (),
__type = “FY”
&& YEAR ( selDate ) – 1
<= YEAR ( date_ ), CALCULATE (
AVERAGEX ( VALUES ( ‘Calendar'[MonthNo] ), [Revenue] ),
FILTER (
ALLNOBLANKROW ( ‘Calendar’ ),
dateMin_ <= ‘Calendar'[Date]
&& ‘Calendar'[Date] <= date_
)
),
__type = “Month”, CALCULATE (
[Revenue],
FILTER (
ALLNOBLANKROW ( ‘Calendar’ ),
selDateMinDATE <= ‘Calendar'[Date]
&& ‘Calendar'[Date] <= selDateDATE
)
) + 0
)
My visual in a report looks like this. Hope the explanation helped you. Download the file to play with.
The BI Journey 