items with no value still showing up

[htpbi]

I am looking at sales data, each line item in data table is a product with a category. When I look at 2020 vs 2019 it still pulls in old categories that have not been sold since 2018 so I have all these rows in my table with no values. I don't have show items with no data checked. Any ideas?

[v-lili6-msft]

HI @htpbi 

From the screenshot, it likes you enable show items with no data for categroy field in matrix, could you please share your sample pbix file for us to have a test, there should be something wrong in other.

Regards,

Lin

[htpbi]

Hi @v-lili6-msft 

I posted a sample file. I dont have show items with no data checked. 

[v-lili6-msft]

hi @htpbi 

The sample file is a live connection to power bi dataset, so we could not open it.

Regards,

Lin

[htpbi]

here is a test file

https://www.dropbox.com/s/43azmuya0scr0ld/test%20report.pbix?dl=0 

[amitchandak]

@htpbi , Hop you do not have overall measure, even this is filtered

refer

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Refer

https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

[amitchandak]

@htpbi , Just saw the last update, have added +0 ?

[Greg_Deckler]

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Pragati11]

HI @htpbi ,

Please share screesnhots around your issue. It's hard to visualise what you are trying to convey here.

Thanks,

Pragati

[parry2k]

@htpbi what are your measures and how you are visualizing it? Maybe it is trying to show 2019 vs 2018 the way you are trying to visualize the data. 

[htpbi]

I have a matrix with category in the rows and then measures in the values. It is filted on 2020 and my measures are like this:

current year=sum(net sales)

last year=IF (HASONEVALUE ( '4-4-5 Calendar'[4-4-5 Year] ),CALCULATE (SUM (net sales),FILTER (ALL ( '4-4-5 Calendar' ),'4-4-5 Calendar'[4-4-5 Year] = VALUES ( '4-4-5 Calendar'[4-4-5 Year] ) - 1&& CONTAINS(VALUES ( '4-4-5 Calendar'[DayOfYear] ),'4-4-5 Calendar'[DayOfYear],'4-4-5 Calendar'[DayOfYear] ))),BLANK ())

Because I am using a retail calendar. All my data flows correctly but if I scroll down on my chart i have all these categories with no data.

[htpbi]