Showing results for 
Search instead for 
Did you mean: 
New Member

"Show items with no data" within a date range

I'm trying to show sales quantity over time. Some months there are no sales, but I want those x-axis headers to show for those months. By checking the option to "show items with no data," it works as intended, except the range of dates it's showing no data is ridiculous. It shows all of 2017 and all of 2020, but I want it to only show items with no data within the min and max months of the visible data.Capture.PNG



Community Support
Community Support



According to your description, i create a simple sample to test:


Please try to create a new calculated table:

X-axis Table = 
    FILTER (
            >= CALCULATE (
                MIN ( 'Table'[Date] ),
                FILTER ( 'Table', 'Table'[Sales] <> BLANK () )
            && 'Table'[Date]
                <= CALCULATE (
                    MAX ( 'Table'[Date] ),
                    FILTER ( 'Table', 'Table'[Sales] <> BLANK () )
    "Date", 'Table'[Date]

Then create relationship between two tables:


Choose this table's [Date] column as column chart visual's X-axis, the result shows:


Hope this helps.


Best Regards,

Giotto Zhi

Super User
Super User


Min Max 1= 
var _min = minx(sales,sales[sales date])
var _max = maxx(sales,sales[sales date])
CALCULATE(sum(sales[Value]),Date[date]<=_max ,Date[date]>=_min )

Min Max 1= 
var _min = minx(sales,sales[sales date])
var _max = maxx(sales,sales[sales date])
CALCULATE(sum(sales[Value]),filter(all(Date),Date[date]<=_max ,Date[date]>=_min ))


Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Thank you Amitchandak.

I had to modify for my use case, posting here as may be helpful to others:

Housekeeping values (KPI) =
//Shows full reporting range in KPI cards
//shows 0 for dates without data (as zero data is not recorded in source)

VAR MinDate = MIN ( 'Report Date'[ExtractDate] )
VAR MaxDate = MAX ( 'Report Date'[ExtractDate] )
        [Avg Value],
        'Report Date'[ExtractDate] <= MaxDate,
        'Report Date'[ExtractDate] >= MinDate
    ) + 0

This is just not working for me and i dont know why. I'm using a column chart and it's still showing the x-axis for ranges where data exists, but outside of the filter. I have no idea what Im doing wrong.




here is the measure:

Days Lost =
VAR MinDate = MIN ( Absences[Absence Start] )
VAR MaxDate = MAX ( Absences[Absence Start] )
        SUM(Absences[Days Absent]),
        Absences[Absence Start] <= MaxDate,
        Absences[Absence Start] >= MinDate
    ) + 0

I'm not familiar with creating variables like this. Can you walk me through how I would set that up?



The formula i provided last time was to create a specific column in order to replace the original X-axis.

I am sorry i haven't shared my test pbix file with you.

Here is my file:



Best Regards,

Giotto Zhi

Solution Sage
Solution Sage

How have you tried to limit the visual to data between your min and max months ?

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

I tried, but it doesn't restrict the range of no item months.

Helpful resources

Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors