X-Axis with Year and as well as Month Year for the near dates in the same graph

Question

I have a dataset that contains monthly values over many years (grouped by month & other attributes so there can be multiple records for each month, although I don't think it matters for the problem) :

       month     val  ...   
1 2017-01-01 17.0700
2 2017-01-01 17.0700
3 2017-02-01  2.4432
.. .........   .....
4 2019-04-01 61.0800
5 2019-04-01 40.7200
6 2019-05-01 20.3600

I can properly visualize monthly data in a bar chart (month is recognized as a date hierarchy with Year and Month in the sub hierarchy)per year/month.

The issue I have is that because the data spans a large period of time, the chart is large and I would like to be able to reduce a year data (e.g. 2017,2018) to its monthly average. The x axis of the result chart would be : avg 2017 - avg 2018 - jan.2019 - feb. 2019 ... The idea is to be able to drill down from a year monthly average back to the monthly values, although I do not know if this is possible?

I managed to get the years monthly average with the following measure :

year_monthly_avg =
            AVERAGEX (
                SUMMARIZE ( data, data[month] ),
                CALCULATE ( SUM ( data[val] )
            )

Which I can display in a separate bar chart (year in the axis).

Siddharth Thanga Mariappan Siddharth Thanga Mariappan · Accepted Answer · 2019-06-28T12:36:08

The Idea is - To do this, you need another table derived from your existing table holding your x-axis values which is going to be on the graph.

I started with a data like this :-

Table 1

So, then lets generate the axis column (Calculated Column) on this Table1:-

AxisCol = 
       Var MaxYear = MAX((Table1[Date].[Year]))
       Var Required_Year = YEAR(Table1[Date])
       Var YearDiff = MaxYear - Required_Year
       Var output = IF(YearDiff = 0, FORMAT(FORMAT(Table1[Date], "MMM YY"),"string"), FORMAT(Required_Year, "@"))
       Return (output)

The data type of this AxisCol is "Text".

Generate the Month Year and Year Column (Calculated Column) in Table 1

Month Year = FORMAT(Table1[Date], "MMM YY") 
Year = YEAR(Table1[Date])

Set the data type of these two columns as "Text" as well.

Create a new table :-

Axis_Table = DISTINCT(Table1[AxisCol])

This has now created the disconnected table.

Now, Create the Date_Col (Calculated Column) to find the Sort Order inside the Axis_Table.

Date_col = 
        Var RequiredVal = VALUE(Axis_Table[AxisCol])
        Var Year_or_Month_year = IF(LEN(RequiredVal) = 4, "Year", "Month Year")
        Var year_split = VALUE("20" & RIGHT(Axis_Table[AxisCol],2))
        Var Month_split = (LEFT(Axis_Table[AxisCol],3))
        Var month_num = SWITCH(Month_split, "Jan", 1, "Feb", 2, "Mar", 3, "Apr"  
               , 4, "May", 5, "Jun", 6, "Jul", 7, "Aug"  
               , 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec"  
               , 12)  
        Var myDate = if(Year_or_Month_year = "Year",DATE(RequiredVal,1,1),DATE(year_split, month_num,1))
        Return myDate

Now, you can create the Sort Order (Calculated Column) in Axis_Table

Sort Order = DATEDIFF((Axis_Table[Date_col]),MAX(Axis_Table[Date_col]),MONTH)

Now the final output as a Measure,

ValueReq = 
            var required_axis_val = SELECTEDVALUE(Axis_Table[AxisCol])
            Var requiredaxis = LEN(required_axis_val)
            Var output1 = CALCULATE(AVERAGE(Table1[Value]), FILTER(Table1,Table1[Month Year] = required_axis_val))
            Var output2 = CALCULATE(AVERAGE(Table1[Value]),FILTER(Table1, Table1[Year] = required_axis_val))
            var out_req = IF(requiredaxis = 4, output2, output1)
            return out_req

This measure can be altered as per your filter selections and other conditions that you might have.

The Final Visual looks like this,

Use the Sort Order in Tooltips and then Sort the axis inside the visual by Sort Order.

You can then create a relationship between the tables on the axis_col for your slicers to work.

You can then differentiate the axis with conditional formatting using this -

conditional formatting = IF(LEN(SELECTEDVALUE(Axis_Table[AxisCol])) = 4,1,0)

Let me know, if you are struggling to understand the solution.

Accept the answer if you can get it to work for you.

X-Axis with Year and as well as Month Year for the near dates in the same graph

1 Answers