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 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

     

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

I tried to adapt it now. I have problems with the first variable already. You are using [Sum Sales], which is a measure i suppose. But then you will only get the date with the last booking, but not YTD. If today is no booking it wont be displayed, correct? So I tried to put in my

_myYTD = IF(DMBI_VdTimetable_1dLag[YTD]=1,DMBI_VdTimetable_1dLag[myDate],BLANK())

here. Did not work because it needs an expression...

 

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

"I tried to adapt it now. I have problems with the first variable already. You are using [Sum Sales], which is a measure i suppose.": Correct, [Sum Sales] is a measure; juts use whatever measure you have.

 

"But then you will only get the date with the last booking, but not YTD. If today is no booking it wont be displayed, correct?". Correct. The first variable returns the last date for which the measure is not blank...

If you want to include dates up to today even if they have no value, use this measure instead:

 

Sum +0 (cutoff) =
VAR _RWS =
    FILTER (
        'Calendar Table',
        'Calendar Table'[Year] = YEAR ( TODAY () )
            && 'Calendar Table'[Date] <= TODAY ()
    ) // creates a table of dates upto and including today for the current year
RETURN
    IF ( COUNTROWS ( _RWS ) = 1, [Sum Sales] + 0 )

 

As regards the comments you included in the previous post, I suspect the YTD column in your Timeline Table has a value of 1 for all dates in the current year (2022).
If the YTD column has a value of 1 for all dates in the current year upto and including today, the filter should work with the simple [measure] + 0: