Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
H3nning
Resolver I
Resolver I

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.

H3nning_2-1662127427789.png

 

 

The graph looks like this:

H3nning_0-1662127231960.png

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:

H3nning_1-1662127373172.png

 

How can I prevent that?

Thanks a lot!

1 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

Result.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

11 REPLIES 11
H3nning
Resolver I
Resolver I

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:
H3nning_0-1662366939730.png

 

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

PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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:

H3nning_1-1662359419207.png

 

 

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

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

Result.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

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

cal.png

 

sum plus 0.png

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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:

H3nning_0-1662380753513.png

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?

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

lbendlin
Super User
Super User

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.