Forum Discussion

H3nning's avatar
H3nning
Icon for Helper V rankHelper 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 time dimension. I also have a column [YTD] which is only 1 if it is this year until now (year to date) and otherwise 0.

 

I use this to filter the line chart to only show dates, that are already over in this year.

 

 

The graph looks like this:

Problem with his is, that missing days are not 0 but are just connected to the next valid datapoint. I want days without data to show as 0.

What I did is to calculate a measure SUM(Column)+0. But then the graph looks like this and the filter on YTD is not applied anymore:

 

How can I prevent that?

Thanks a lot!

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

     

11 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    To report on things that aren't there you need to use disconnected tables and crossjoins.

    • H3nning's avatar
      H3nning
      Icon for Helper V rankHelper V
      4 years ago

      Hi, what do you mean? Can you elaborate how to do this?

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity 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
      Icon for Helper V rankHelper 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
      Icon for Helper V rankHelper 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
        Icon for Community Champion rankCommunity 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
    Icon for Helper V rankHelper V
    4 years ago

    I tried another approach, but I still have the plroblem that to many dates are displayed.

     

    What did I do? 

     

    First I created a new Date column with blanks if it is not YTD:

     

    _myYTD = IF(DMBI_VdTimetable_1dLag[YTD]=1,DMBI_VdTimetable_1dLag[myDate],BLANK())
     
    Then I created a measure for the y axis using allexept:
     
    __Measure =

    CALCULATE(
            SUM(DMBI_VfSachposten[Habenbetrag])+0
            ,ALLEXCEPT(
                DMBI_VfSachposten,
                DMBI_VfSachposten[Mandant_ID],
                DMBI_VdTimetable_1dLag[YTD],
                DMBI_VdTimetable_1dLag[_myYTD],DMBI_VdTimetable_1dLag[_myYTD].[Month],DMBI_VdTimetable_1dLag[_myYTD].[Year],DMBI_VdTimetable_1dLag[_myYTD].[Quarter],DMBI_VdTimetable_1dLag[_myYTD].[Day]
            )
            ,DMBI_VdTimetable_1dLag[YTD]==1
            ,DMBI_VfSachposten[Mandant_ID]==1
    )
     
    Mandant_ID is just another filter I need because i want to display several lines of different companys later.
     
    What i have now is this, if i drill down to days:

     

    It is almost what I want, but why is the line continued until end of the year???