Blank Row with the Total's Result

[rawiswarden]

Hello,

I am creating some moving averages using parameters after recieving some guidance here (https://community.powerbi.com/t5/Desktop/Rolling-Average-of-Measure/td-p/881963). 

The problem I am having now is that there is a calculation that appears at the top of the table.  I'm assuming I've made a rookie mistake, but I haven't had luck finding the terms to describe this in my Google search.

The calculation is below:

APR (Moving Avg) = CALCULATE([APR (Avg)],DATESBETWEEN(Calendar_Dates[Date],eomonth(MIN(Calendar_Dates[Date]),-'MA Length'[MA Length Value])+1,MAX(Calendar_Dates[Date])),userelationship(Facts_Originations[PortStats.BookedDate], Calendar_Dates[Date]))

For each of these similar calculations, I get a blank row at the top ofthe table with the total's calculation.  Is this a common error or is this a bug in the way I've structured my data model?

[v-juanli-msft]

Hi @rawiswarden 

As tested with information from your two threads, i can't reproduce your problem.

To get proper visual without a blank row, you could edit the visual level filter as below

Also make sure you add "month year' or any "date" columns from "calendar" table to the visuals.

To get a close look at your problem, you could delete the privacy information form the pbix and share it here.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rawiswarden]

Hello!

Suddenly this issue fixed itself.  Perhaps I wasn't using the most recently refreshed dataflow or something.  But the null date must be the issue, so I accepted that answer.

Thanks!!

[az38]

Hi @rawiswarden 

it looks like either PortStats.BookedDate or Calendar_Dates[Date] has a null value or value out of range MonthYear column.

check it. try to remove null values from these columns using Power Query Editor Mode

do not hesitate to give a kudo to useful posts and mark solutions as solution

[rawiswarden]

Hello @az38 

I have removed any empty BookedDates with Power Query.  Is there a way to verify there are no nulls in that column?  I have checked and it doesn't look like it.  The issue is still occuring.  Calendar_Dates is generated via this, so I do not believe any nulls exist: CALENDAR (min(Dimension_PortStats[BookedDate]), MAX(Dimension_PortStats[BookedDate])),

[az38 · Accepted solution]

@rawiswarden 

as a monthyear you are using not date field, but any other attribute (maybe calculated). are you sure it not null everywhere?

use "Data" mode to check (left pane - reports/data/relationships)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[rawiswarden]

@az38 

Hello, I do not see any nulls.  Just to be sure, I went to the dataflow and changed the SQL query to not import any data where BookedDate is null, since I do know there are two or three nulls there from when I posted my first example.

The issue happens regardless of whether I use a date column or the month-year column.  Is there any other common cause I can look for?

[az38]

@rawiswarden 

what statement defines your 

APR (Avg)

column?

you also could share your pbix-file to look closely

do not hesitate to give a kudo to useful posts and mark solutions as solution