Show every month of the year in the x-axis independent of slicer selection

[JayVee]

Hi All,

How can I create a chart where the x-axis shows all the months of the selected year (e.g. 2021) even when the month slicer is active?

In the image above I have the result I want, and in the image below is what I actually get from PowerBI when selecting different months from the date slicer:

Note that in this case I'm using a DATESYTD approach to calculate the monthly aggregates and I have already created an independent date table so that all months are displayed independent of the selected slicer.

However, it's only showing the months included in the DATESYTD and I can't find a way to include all the missing months from that year even when tring to filter the data with other functions like ENDOFYEAR or [Date]=MAX([Year]):

Many thanks! Jay

[Anonymous · Accepted solution]

Hi  @JayVee ,

I created some data:

Here are the steps you can follow：

1. Create a calendar table as a slicer.

Table 2 = DISTINCT('Table'[Date])

2. Create measure.

Flag =
var _selectyear=
SELECTEDVALUE('Table 2'[Date].[Year]) in VALUES('Table'[Date].[Year])
return
IF(
ISFILTERED('Table 2'[Date].[Year]),IF(_selectyear,1,0),1
)

Amount =
if(SELECTEDVALUE('Table'[Date].[Month]) IN VALUES('Table 2'[Date].[Month]),   SUM('Table'[rand]),0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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

[JayVee]

Hi @Anonymous ,

Thats exactly it! I have updated the measure to take into account all the months previous to the Single Selection in the slicer and it worked perfectly!

I just had to create another calculated column with the month number and then include the <= operator in the measure formula:

Amount = IF(
            SELECTEDVALUE('Table'[Month Number]) <= VALUES('Table 2'[Month Number]),
            SUM('Table'[rand]),
            0
           )

Great job and many thanks for that!

[JayVee]

To help further, here is a sample of the data as well as the PBIX where I try to solve this point:

PBIX file

CSV file

[amitchandak]

@JayVee , Try to add +0 to you measure or

use this option

if need check

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

[JayVee]

Hi @amitchandak ,

That would work if the next months didn't contain any data. However, they are fully populated already as you can see in the image below for when I select "December" in the slicer:

What I want to do is kind of "filter out" some of these months based on the slicer, but still showing the full x-axis even for the months which will be now filtered out. That would result in the picture of the first post: