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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
captrut
Frequent Visitor

Show Items with No Data injecting Dates

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

captrut_0-1676658424117.png

 

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

 

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

 

Thanks

 

 

1 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

 

 

 

View solution in original post

7 REPLIES 7
captrut
Frequent Visitor

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

 

captrut_0-1676907879810.png

 

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

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.

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. 

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

 

 

 

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:

captrut_0-1677010732702.png

 

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!

 

Hi @captrut 

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

Sorry about that.

grantsamborn
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.