Plotting line chart with time-series data, time values in column names

[liuy70]

I am tring to plot a line chart to display the trend of average daily usage of our website for each user. The data format looks like following: 

Each row is a user and I need to first calculate the average daily duration for each day (01/08, 01/09, etc.) and then plot each average to a line chart. 

The plot I am trying to replicate looks like this:

 Is it possible that I can achieve this in Power BI?

Thanks for the help!

[MFelix · Accepted solution]

Hi @liuy70,

You need to unpivot your columns and convert it to dates so you can have a way of doing the correct formatting.

Start by selecting all the columns except the daily values and then do a unpivot other columns you will get two columns Attribute and Values. Change the attribute values so you only get the dates and then you can use them on a visual

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgsytLQ0NrBU0lFKBGJXIPZ1DwFR/iUZqUVA2tAARJgCCSMDpVgdkBYjAwOIliQgDgZiZw9XX0JaDCFakmFagp09cWqJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cohort = _t, RIN = _t, College = _t, Major1 = _t, #"COhort COde" = _t, #"Daily_Duration_01/08" = _t, #"Daily_Duration_01/09" = _t, #"Daily_Duration_01/10" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Cohort", "RIN", "College", "Major1", "COhort COde"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Daily_Duration_","",Replacer.ReplaceText,{"Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute", "Date"}})
in
    #"Renamed Columns"

[amitchandak]

@liuy70  , Assume need Avg of all users on a daily basis. if the trend is on date

Averagex(Values(Table[USer]), calculate(Sum(Table[daily usage])) )

If user is on axis

Averagex(Values(Table[Date]), calculate(Sum(Table[daily usage])) )