Creating a Quarterly Dashboard using a Line and Cluster

[GOHOCKEY]

Hello,

I'm very new with Power BI,

I'm trying to create a Monthly and Quarterly Report with a line showing the time it took for selling the cars. I have the following,

CAR ID CAR_SOLD_DATE TOTAL_ CARD_ SOLD HOW_LONG (DAYS)

111      2015/01/01           200                                5

112      2015/03/01            100                               20

113      2015/05/05            400                               40

114      2016 /06/01           90                                 50

115      2016/09/01           20                                  120

116      2016/11/01           190                                80

117      2017/01/04           290                                66

118      2017/05/01           400                                 33

118      2018/01/01           200                                22

119      2018/12/01           100                                300

120      2018/09/05           300                                290

SO, it seems like the Line and Cluster Chart is a good choice. But I'm not sure how to go about the creation of the new columns for showing the data in Quarters and Months for the Chart using the CAR_SOLD_DATE . I'm also not sure how to have a Line showing How Long Days on this Chart.

Please help if you could.

Thanks so much!!

[Seward12533 · Accepted solution]

You need to create a date table that contains the various DATE paramaters you want to display by and then use those to build your visuals. 

Here are a few links

https://www.youtube.com/watch?v=gyvhM5eiT0U

IF you need more or want formulas for lots of obscure date properties check this out

https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

[v-yulgu-msft · Accepted solution]

Hi @GOHOCKEY,

You can create a calendar table, establish a one to many relationship from calendar table to data table based on date column. Add date column from calendar table to X-axis of chart, it will automatically show date hierarchy. When you drill down to Quarter or Month level, the value added to Y-axis will be summarized automatically Quarterly or Monthly.

Best regards,

Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @GOHOCKEY,

You can create a calendar table, establish a one to many relationship from calendar table to data table based on date column. Add date column from calendar table to X-axis of chart, it will automatically show date hierarchy. When you drill down to Quarter or Month level, the value added to Y-axis will be summarized automatically Quarterly or Monthly.

Best regards,

Yuliana Gu

[GOHOCKEY · Accepted solution]

Thanks so much! I'm new but looking into both recommendations.

[Seward12533 · Accepted solution]

You need to create a date table that contains the various DATE paramaters you want to display by and then use those to build your visuals. 

Here are a few links

https://www.youtube.com/watch?v=gyvhM5eiT0U

IF you need more or want formulas for lots of obscure date properties check this out

https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/