Forum Discussion

Alberto76's avatar
Alberto76
Regular Visitor
9 years ago
Solved

line chart not representing dates without data

Hi all, I've searched through the posts and around internet but I didn't found a similar problem like mine solved. I have a DB containing IDs of services and related quantities over months and year...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi Alberto76,

     

    If there are no data records in a month,  the line chart will not represent dots for those month. In my test, I created a calendar table and crossjoin it with the DB table. However, I think this workaround is not appropriate to the dataset which contains very large records.

     

    In this crossjoin table, I added a calculated column:

    Column = IF('Table 5'[Year_Month]='Table 5'[Date],'Table 5'[Qty],'Table 5'[QTY2])

     

    New another calculate table only contains ID, Year_Month and Qty columns:

    Table 6 = SELECTCOLUMNS('Table 5',"ID",'Table 5'[ID],"Date",'Table 5'[Date],"QTY",'Table 5'[Column])

    Then, I put the running total measure in Value, the dates column in Axis. I got the below output, the line chart represents a continuous line with dots for everyday.

     

    Thanks,
    Yuliana Gu

     

     

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi Alberto76,

 

If there are no data records in a month,  the line chart will not represent dots for those month. In my test, I created a calendar table and crossjoin it with the DB table. However, I think this workaround is not appropriate to the dataset which contains very large records.

 

In this crossjoin table, I added a calculated column:

Column = IF('Table 5'[Year_Month]='Table 5'[Date],'Table 5'[Qty],'Table 5'[QTY2])

 

New another calculate table only contains ID, Year_Month and Qty columns:

Table 6 = SELECTCOLUMNS('Table 5',"ID",'Table 5'[ID],"Date",'Table 5'[Date],"QTY",'Table 5'[Column])

Then, I put the running total measure in Value, the dates column in Axis. I got the below output, the line chart represents a continuous line with dots for everyday.

 

Thanks,
Yuliana Gu

 

 

Alberto76's avatar
Alberto76
Regular Visitor
9 years ago

Hi Youliana,

sorry, the solution was quite simple: using CROSSJOIN formula...

 

Thank you again!

Best regards

Alberto