Forum Discussion
Slicer / Date Issue
I am adding data to a line graph to show how many of a certain complaint type has come in over 12 months.
I have a filter on the visual to filter by the last 12 calendar months of receipt date and I have a slicer by complaint type so that a complaint type from a list can be selected and show just that line on the graph. Some complaint types may have occurred in one of the 12 months only but I'd like the x axis to remain the same when selecting that complaint type so on the 'receipt date' in the x axis I have selected show items with no data but I still get certain complaint types that when I select them, not all of the months are included on the x axis. For example in this screenshot, the data goes up to Feb 2025 but this line stops at Jan 2025.
I am self taught at Power BI so I'm sure this is probably something very basic that I'm missing!
Hey there!
Here are a few solutions for you:
1. Since your x-axis is Receipt Date (Month, Year), make sure your dataset has a continuous date table. Power BI needs a proper date table to maintain continuity. Create a Date Table (if you don't have one):
DateTable = ADDCOLUMNS(
CALENDAR(DATE(2023,1,1), DATE(2025,12,31)),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMM YYYY")
)Go to Modeling → Mark as Date Table → Select Date.
Use This Date Table in Your X-Axis: Instead of Receipt Date from your complaint dataset, use DateTable[Month].Another reason why months disappear is that Power BI does not plot blank values. You can modify your measure to return 0 when no data is available. Try this formula: Complaint_Count =
VAR SelectedComplaints = SELECTEDVALUE(Complaints[ComplaintType])
RETURN
IF(
NOT(ISBLANK(SelectedComplaints)),
COUNT(Complaints[ReferenceNumber]),
0
)This ensures that even when no complaints exist for a given month, Power BI plots 0 instead of removing the month.
Hope this helps!
Cheerio,
Z 😁😁
3 Replies
Hey there!
Here are a few solutions for you:
1. Since your x-axis is Receipt Date (Month, Year), make sure your dataset has a continuous date table. Power BI needs a proper date table to maintain continuity. Create a Date Table (if you don't have one):
DateTable = ADDCOLUMNS(
CALENDAR(DATE(2023,1,1), DATE(2025,12,31)),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMM YYYY")
)Go to Modeling → Mark as Date Table → Select Date.
Use This Date Table in Your X-Axis: Instead of Receipt Date from your complaint dataset, use DateTable[Month].Another reason why months disappear is that Power BI does not plot blank values. You can modify your measure to return 0 when no data is available. Try this formula: Complaint_Count =
VAR SelectedComplaints = SELECTEDVALUE(Complaints[ComplaintType])
RETURN
IF(
NOT(ISBLANK(SelectedComplaints)),
COUNT(Complaints[ReferenceNumber]),
0
)This ensures that even when no complaints exist for a given month, Power BI plots 0 instead of removing the month.
Hope this helps!
Cheerio,
Z 😁😁
Hello,
Thank you so much for your speedy response. I have tried your steps in an example sheet but am getting this:
Hi ,
1.Create a new measure to count rows
Countref = COUNTROWS('TableName')+02. Create a date table which has the duration you need
3. Make a relationship between the date table and your ref table
4. use date table date column as X-axis & use newly created measure as Y-axis
5. right click on the x-axis date column and select show items with no data
output should appear similar to this
