"Show items with no data" within a date range

[walkerstorrer]

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.

[v-gizhi-msft]

Hi,

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

Please try to create a new calculated table:

X-axis Table = 
SELECTCOLUMNS (
    FILTER (
        'Table',
        'Table'[Date]
            >= 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

[amitchandak]

Try

Min Max 1= 
var _min = minx(sales,sales[sales date])
var _max = maxx(sales,sales[sales date])
return
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])
return
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

[Dvayro]

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] )
RETURN
    CALCULATE (
        [Avg Value],
        'Report Date'[ExtractDate] <= MaxDate,
        'Report Date'[ExtractDate] >= MinDate
    ) + 0

[kmrastegar]

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] )

RETURN

    CALCULATE (

        SUM(Absences[Days Absent]),

        Absences[Absence Start] <= MaxDate,

        Absences[Absence Start] >= MinDate

    ) + 0

[walkerstorrer]

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

[v-gizhi-msft]

Hi,

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:

pbix 

Best Regards,

Giotto Zhi

[kentyler]

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

[walkerstorrer]

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