Forum Discussion

H3nning's avatar
H3nning
Helper V
4 years ago
Solved

Replacing missing data with 0 but keeping filter

Hi,   I want to build a line chart. In that chart i plot the values of a column. I have data for 2 years.   In addition i have a timetable with all days from 2010 unteil 2030. I use this as a tim...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    The reason you are seeing the seeing the line into the dates without a value is because your Timeline table includes dates upto 2030 (so you will be a 0 value in the chart for dates in 2022 with no bookings.

    The way to solve this is to change the measure so that it filters out the future dates with no values-

    For example (my Timeline Table is Called Calendar Table)

    Sum +0 (cutoff) =
VAR _MaxDate =
    CALCULATE (
        LASTNONBLANK ( 'Calendar Table'[Date], [Sum Sales] ),
        ALL ( 'Calendar Table'[Date] )
    ) // Calculates the last date in the data table which has a value
VAR _RWS =
    FILTER ( 'Calendar Table', 'Calendar Table'[Date] <= _MaxDate ) // creates a table of dates upto and including the last date with a value
RETURN
    IF ( COUNTROWS ( _RWS ) = 1, [Sum Sales] + 0 )
// The sum + 0 is applied to dates on or before the max date with a value, if not returns blank

    You can now also use the continuous x-axis setting

     

PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

In the Formatting Pane, try changing the x-axis from continuous to categorical, using the original measure (not the + 0 one)

  • H3nning's avatar
    H3nning
    Helper V
    4 years ago

    Hi, that unfortunaltely just leaves out the days without bookings, but they are supposed to show as 0 not left out...

  • H3nning's avatar
    H3nning
    Helper V
    4 years ago

    Its not the end, but it brought me one step further. This is how it looks like when I kick out the original measure and use the +0 again:

     

     

    Weekends are reported with 0 correctly. Onyl issue is, that the YTD Filter seems not to work. It shows complete 2022 (why not all the years again btw?). I guess that has something to do with the calculate statement I used there, which kills all filters right?

     

    Whats also not optimal is, with changing to continous it forces PBI to display and label all datapoints all the time. The diagram is not scaled anymore...

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      4 years ago

      The reason you are seeing the seeing the line into the dates without a value is because your Timeline table includes dates upto 2030 (so you will be a 0 value in the chart for dates in 2022 with no bookings.

      The way to solve this is to change the measure so that it filters out the future dates with no values-

      For example (my Timeline Table is Called Calendar Table)

      Sum +0 (cutoff) =
VAR _MaxDate =
    CALCULATE (
        LASTNONBLANK ( 'Calendar Table'[Date], [Sum Sales] ),
        ALL ( 'Calendar Table'[Date] )
    ) // Calculates the last date in the data table which has a value
VAR _RWS =
    FILTER ( 'Calendar Table', 'Calendar Table'[Date] <= _MaxDate ) // creates a table of dates upto and including the last date with a value
RETURN
    IF ( COUNTROWS ( _RWS ) = 1, [Sum Sales] + 0 )
// The sum + 0 is applied to dates on or before the max date with a value, if not returns blank

      You can now also use the continuous x-axis setting

       

      • H3nning's avatar
        H3nning
        Helper V
        4 years ago

        Thanks PaulDBrown I will rebuild this for my case as soon as I find the time and test it. But please let me ask a question, just to become better: Why ist it not working with my approach? All I want to do is cutt of all datapoints on the x axis, that have a certain value in my timetable (YTD =0). I mean I can do the same thing by just righ click and exclude the datapoints. I dont get, why this is not possible in code:

        I understand that my bookings have the value 0 until 2030 because of my query. But why cant I filter on the date variable in the diagram? Do I misunderstand some basic principle of PBI? My timetable begins at 2010. Why are these years and other years in the future not displayed?