Show Items with No Data injecting Dates

[captrut]

Hi all, 

Thanks in advance for any help.

I'm trying to show sales data by date in a stacked bar chart and I would like to be able to filter by specific customers, so far no problem. I would like to keep the x-axis constant when filtering as customers do not order every month.

Bar chart without "Show items with no data" enabled

Enabling "Show items with no data" works as expected, leaving gaps where there are no sales, but also adds the rest of 2023 and dates going back to 2017 for which there are no sales recorded in my data set.

Additionally, date slicers have no effect on the x-axis. 

How can I stop these extra dates from being displayed in my visualizations?

Thanks

[captrut]

@grantsambornthanks for the tip, unfortunately the visual seems to ignore all filters when determining the date range. 

I found a data point responsible for the early dates which was filtered out, so I exluded that in power query and that fixed the early dates, however I'm still seeing the rest of 2023.

I have verified several times that there are no other dates in other fields in the data set and there are no future dates.

[grantsamborn]

Hi @captrut 

Is there any way you could duplicate this problem with sample data?

Without seeing an example, I'm not sure how much I can help.

[captrut]

Thanks, @grantsamborn 

I've attached a clean pbix with data embedded (I think). Dropbox Link 

It seems to me that because I'm using a Date hierarchy, powerbi wants to display the FULL year, even when there is no data. I haven't had a chance to test this as I'm not fluent in DAX and haven't had time to build a summary table to test.

Thanks again, it's really appreciated. 

[grantsamborn · Accepted solution]

Hi @captrut 
In my opinion, the problem has to do with using the "Automatic Time Intelligence" instead of a date table.

I used a simple DAX date table and everything seems to work fine.

Unrelated: You might want to append the yearly tables and unpivot. I included a table called Unpivot that you might want to take a look at.

Let me know what you think.

Clean Sample - 1.pbix

[captrut]

HI @grantsamborn,

Thanks for your help! That seems to work, but I changed the Date table to:

Date = 
GENERATE(
    CALENDAR(
        MIN('Total Sales'[Date]),
        TODAY()
            ),
    VAR d = [Date]
    RETURN
        ROW(
            "Year", YEAR( d ),
            "MonthNo", MONTH( d ),
            "Month",
                FORMAT( d, "mmmm" ),
            "MON",
                UPPER( LEFT( FORMAT( d, "mmm" ), 3) ),
            "YearMonth",
                FORMAT( d, "yyyymm" ),
            "Week",
                FORMAT( d, "yy" ) 
                    & "-"
                    & FORMAT( WEEKNUM( d, 2 ), "00" ),
            "MMM-YY",
                FORMAT( d, "mmm-yy" )
        )
)

The CalendarAuto was generating dates through the end of 2023 which was displaying when the "show items with no data" was checked:

Still have no idea why the PBI was generating those dates on it's own, but you're right, using a date table seems to fix that.

Really really appreciate the help, marked as solved!

[grantsamborn]

Hi @captrut 

I missed the calculated column in the date table and had to update the link.

Sorry about that.

[grantsamborn]

Hi @captrut 

I'm not sure if this helps but I often add a column to my date table like this:

_InPeriod =
	IF(
		[Date] >= MIN( 'SalesData'[Date] )
			&& [Date] <= MAX( 'SalesData'[Date] ),
		TRUE(),
		FALSE()
	)

Then you can filter your visual with this column.