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.
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.
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
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
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:
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:
How have you tried to limit the visual to data between your min and max months ?
Help when you know. Ask when you don't!
I tried, but it doesn't restrict the range of no item months.
Check out the changes to the Power BI Community announced at Build.
Find out more about the May 2023 update.
Visit our Data Stories Gallery and give kudos to your favorite Data Stories.