Replacing missing data with 0 but keeping filter

[H3nning]

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!

[H3nning]

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

[PaulDBrown]

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

[H3nning]

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 · Accepted solution]

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]

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]

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

[H3nning]

__SachpostenKPYTDV3 =

VAR v_MaxDate=

CALCULATE(LASTNONBLANK(DMBI_VdTimetable_1dLag[myDate], MIN(DMBI_VdTimetable_1dLag[_myYTD])),ALL(DMBI_VdTimetable_1dLag[myDate]))

VAR v_RWS= FILTER(DMBI_VdTimetable_1dLag,DMBI_VdTimetable_1dLag[myDate]<=v_MaxDate)

Return

IF(

    COUNTROWS(v_RWS)>0

    ,CALCULATE(

        SUM(DMBI_VfSachposten[Habenbetrag])+0

        ,DMBI_VfSachposten[Mandant_ID]=1

    )

)

It worked with Min though. It just frustrates me, that I don't understand why...

But thanks a lot!

[H3nning]

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?

[H3nning]

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

[lbendlin]

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

[H3nning]

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