Add in Months with 0 Count to Clustered Column Chart but restrict months outside Slicer Selection

[bdehning]

I have the following Clustered Column Chart.  

Users select the numbers of years they want to see on a Slicer.  It could be 1 year up to 9.   I have set a filter to only show the most recent 72 months, if users select more than 6 years on the slicer. 

It works fine, except that it does not show the months with a 0 count.  Example, August 2021 is not showing.

I use a simple Count measure.

I have tried using my Count measure +0.  I do get the months I want, but I also get all 0 Months that are out the Slicer selected years but within the filter 72 months. 

I have not figured out a way to filter out the 0 count months that are outside the Slicer selected years and keep the ones I want inside the Slicer selcted years.    

How can I solve this? 

[bdehning]

123ABC,

I started playing around with a fixed Selected Number of Months to be used by the CountWithFilter Measure.   My Year field and Month Fields for X Axis are both Text fields.  Does that matter?     

[123abc]

If your Year and Month fields for the X-axis are both text fields, it can affect the sorting and display in your chart. In Power BI, it's generally a good practice to use numerical or date fields for the X-axis when working with clustered column charts to ensure proper sorting and formatting.

Here are a few considerations:

1. Sorting: Text fields may not sort correctly chronologically. For example, "Jan 2023" may come before "Feb 2022" in a text-based sort. To ensure correct sorting, consider converting your Month field to a numerical format (e.g., 01 for January, 02 for February).

2. Formatting: Text-based X-axis labels may not display as cleanly as date-based labels. You may want to consider formatting the labels to display only the year or year-month combination for better readability.

To address these issues, you can follow these steps:

1. Change Data Types: Ensure that your Year and Month fields are appropriately formatted as numbers or dates, not text. In Power BI, you can change the data type of columns in the "Model" view.

2. Create a Combined Date Field: If your data source doesn't have a date field, you can create a new calculated column that combines the Year and Month fields into a date format. For example:

Combined Date = DATEVALUE('YourTable'[Year] & "-" & 'YourTable'[Month] & "-01")

1. This calculated column assumes your Year and Month fields are numbers. Adjust the formula if they are still in text format.

2. Use Date or Numeric Fields for X-Axis: In your clustered column chart, use the new combined date field or the numeric Year and Month fields as the X-axis categories. This will ensure proper sorting and formatting.

By using date or numeric fields for your X-axis, you'll have better control over sorting and formatting, making your chart more user-friendly and accurate.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[123abc]

Create a Measure for Month Selection: Create a measure that calculates the number of selected months based on the slicer selection. You can use the SELECTEDVALUE function to get the number of selected years from the slicer and then multiply it by 12 (months per year). This measure will help us determine the most recent 72 months to display.

SelectedMonths = SELECTEDVALUE(SlicerTable[SelectedYears]) * 12

Create a Measure for Count with Filtering: Now, create a new measure that calculates the count for each month, taking into account both the count data and the selected months. You can use the following DAX formula:

CountWithFilter =
VAR SelectedMonthsCount = [SelectedMonths]
RETURN
IF(
COUNTROWS(ALL('DateTable')) <= SelectedMonthsCount,
[Your Count Measure],
IF(
MAX('DateTable'[Date]) > CALCULATE(MAX('DateTable'[Date]), ALL('DateTable')) - SelectedMonthsCount,
[Your Count Measure],
BLANK()
)
)

Plot[Your Count Measure] with the actual DAX measure you are using to calculate the count.

Update the Clustered Column Chart: In your clustered column chart, use the DateTable[Date] column on the Axis and the CountWithFilter measure you just created for the Values. This will display the count for each month within the selected years and show 0 counts for months within the most recent 72 months.

Set Slicer and Test: Make sure you have the slicer for selecting years in your report. Users can select the number of years they want to see, and the chart will adjust accordingly, displaying the most recent 72 months with 0 counts as needed.

By following these steps, you should be able to display months with 0 counts within the selected years while restricting them to the most recent 72 months based on slicer selections.

 If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[bdehning]

I think this has possibilities.  

Here is how my Slicer works as Users Select Policy Inception Dates, which are usually 12 month periods and determines the numer of months as it is not by numbers.

Is there something to add to the measures to count number of Policy Inception Dates first?