Create line chart for Quaterly

[Anonymous]

Hello,

I want to create a line chart for Quarterly;

I have 4 column data ;

Q1, Q2, Q3, and Q4 (all the column types are decimal numbers)

I don't have a date column, so I create a table for Quarter axis;

Date = CALENDAR(
DATE(2020,1,1),
DATE(2021,1,1)
)

of course, I need to create a new measurement to made it show in the line chart.

when I put the column directly to values, it will not represent the quarter

anyone could help me with this?

Thank you

[amitchandak]

@Anonymous , Based on what I got add additional QTR column in date table and use that

Date = addcolumns( CALENDAR(
DATE(2020,1,1),
DATE(2021,1,1)
) ,"Qtr" , "Qtr" & format([Date], "Q"))

[Anonymous]

Hi @amitchandak thank you for the reply, but how I can make the line chart with 4 columns as mentioned above 😄

I want to create 1 line chart with 4 dots (following the data from the 4 columns)

[v-yingjl]

Hi @Anonymous ,

You can try:

1. Unpivot these four columns in Power Query first:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY65FcAgDEN3oabAByTMwmP/NWLJkMaFji+vVcRKLTbiSI+jo+waaoP6xvEHllM1ZEWgOvxO1SnAUvqaBCSUZWAkuRzS9nOTEJUQesYqxpmdd4hWsJjFmsl973B95u+HEK29Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Quarter"}})
in
    #"Renamed Columns"

2. Create a Date table like this:

Date =
ADDCOLUMNS (
    CALENDAR ( "2020/1/1", "2021/1/1" ),
    "Quarter", "Q" & QUARTER ( [Date] )
)

3. Build relationship between these two tables based on the Quarter column.

4. Create a line chart

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl 

Can I put year and custom text after Q1/2/3/4 ?

Date =

ADDCOLUMNS (

CALENDAR ( "2020/1/1", "2021/1/1" ),

"Quarter", "Q" & QUARTER ( [Date] )

)

Since my Quarter column name format is Q# 2020 - ScoreB, I can't create a relationship between both table

[amitchandak]

@Anonymous , One way is to have column in you table in Power Query

Number.ToText(Date.Year([Date])) &" -Q" & Number.ToText(Date.QuarterOfYear([Date]))

And unpivot that in power query

https://radacad.com/pivot-and-unpivot-with-power-bi

Or create 4 measure

Q1 2021 = Calculate(Sum(Table[Value]), filter(Date, Date[QTR Year] = "2021-Q1"))

Create others 

or use time intelligence

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last and Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))

next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))