Forum Discussion

captrut's avatar
captrut
Frequent Visitor
3 years ago
Solved

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

 

 

Need Help
  • grantsamborn's avatar
    grantsamborn
    3 years ago

    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

     

     

     

7 Replies

  • grantsamborn's avatar
    grantsamborn
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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.

  • captrut's avatar
    captrut
    Frequent Visitor
    3 years ago

    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's avatar
      grantsamborn
      Icon for Solution Sage rankSolution Sage
      3 years ago

      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's avatar
        captrut
        Frequent Visitor
        3 years ago

        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.