Not able to show other months once filter data with no months

[chinaik123]

Hi, 

I am having troubles to show in a chart those months that has no values.

I read from somewhere this in forum that to [Show Values with No data] but it is still not appearing.

Please help me as it looks like a very wrong representation of a chart.

[danextian]

Hi @chinaik123 ,

Show items with blank values applies only to rows that exist in your table. For example, if your table has a month column but there isn't a row for January, that missing row will not magically appear in your visual. A row with blank value is different from a row that doesn't exist.  That said, use a separate dates table and use the month from that table in your visual. You can use CALENDAR function to do that. Sample calculated table:

Dates =
VAR __BASE =
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) ) --replace the second date with today if you want the dates to be until today
RETURN
    ADDCOLUMNS (
        __BASE,
        "Year", YEAR ( [Date] ),
        "Month", FORMAT ( [Date], "mmmm" ),
        "Month Sort", MONTH ( [Date] ),
        --use this to sort Month by
        "Month and Year", FORMAT ( [Date], "mmm-yy" ),
        "Month and Year Sort", FORMAT ( [Date], "YYYYMM" ) -- use this to sort Month and Year by
    )

Make sure to create a relationship between this dates table and your fact table.

You may create another measure by appending +0 to your existing measure  (eg new measure = [old measure] + 0) in the visual so it shows zero instead of blank when the value is blank. By default, Power BI hides rows with blank values. There'd be no need to show items wi th blank values if you use this method.

[chinaik123]

Hi @danextian 
Thank you it seems to be working , however, can i sort by Fiscal Month instead? 
Meaning that the Month starts from April instead of Jan.

Thanks in advance.

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solutions @Ashish_Mathur  and @danextian  provided, and i want to offer some more information for user to refer to.

hello @chinaik123 , based on your description, you want to sort the month by fiscal month, you can refer to the following solution.

Create a calendar table.

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthNo", MONTH ( [Date] ),
    "Quarter", QUARTER ( [Date] ),
    "FY", YEAR ( EDATE ( [Date], -3 ) )
)

Then add a rank column in calendar table

Rank =
VAR a =
    SUMMARIZE ( 'Calendar', [MonthNo] )
VAR b =
    ADDCOLUMNS (
        a,
        "Flag",
            SWITCH (
                [MonthNo],
                1, 10,
                2, 11,
                3, 12,
                RANKX ( FILTER ( a, [MonthNo] >= 4 ), [MonthNo],, ASC )
            )
    )
RETURN
    MAXX ( FILTER ( b, [MonthNo] = EARLIER ( [MonthNo] ) ), [Flag] )

Then select the Month column and select sort by Rank column

2.Create a relationship between the data table and calendar table.

3.In calendar table, create a new hierachy based on the fiscal year.

Then put this hierachy to the x-axis.

Output

Best Regards!

Yolo Zhu

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

[Ashish_Mathur]

Hi,

Drag this measure to your visual

Measure = coalesce([your measure],0)

Hope this helps.

[Ashish_Mathur]

Share the download link of the PBI file.

[chinaik123]

Hi 
I am also not able to get it after using the measure.