Forum Discussion

ms92ita's avatar
ms92ita
Icon for Helper I rankHelper I
3 years ago

line chart with dates and missing data

Hi,   I am getting some troubles on a report with direct query connection from a SQL server. In this report i have a slicer of dates (between) and some charts/counters. Specifically I have a line ...
Need Help
Tips and Tricks
ms92ita's avatar
ms92ita
Icon for Helper I rankHelper I
3 years ago

As you can see, the granularity is per day and not aggregated... The visual is for a year.

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    3 years ago

    ms92ita can you share your pbix file or sample data in table format which can be copied

    • ms92ita's avatar
      ms92ita
      Icon for Helper I rankHelper I
      3 years ago

      PBIX is a bit complex to export beacuase the the chart a little part of a bigger report and I do not know how to export data from a calculated table. Anyway, the structure of the source data for time line is this:



      X axis is date, while Items and TotalItems are line values

      Another example is from FirstDate and LastDate as y-axis:

       

       

       

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi ms92ita

         

        Please create the following measures for Items and TotalItems. They will return 0 for blank values. Use these measures for line values to replace the original columns. 

        M_Items = SUM ( 'TableName'[Items] ) + 0
        M_TotalItems = SUM ( 'TableName'[TotalItems] ) + 0

         

        Best Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as Solution to help other members find it.