Matrix view with 3 letter formatted month sorted in orde (without losing months that have a 0 value)

[SamIsh]

Hey, 

I've been having an issue with a matrix that identifies the number of items that occur each month.
 
Initially, I had the month from a date hierarchy populating across the top. This meant that I could populate 0 values into the matrix with "Show items with no data" and the calculated column below.

 Count = COUNT('Data'[Item ref]) + 0

However, I needed more space available on the Dashboard. I applied a sort to a 3-letter month format for that date field to populate January as Jan, February as Feb, etc. Doing such, however, has caused my 0 values to disappear from the count. As a result, when I filter on a specific attribute, my table goes from

to this...

Can we force format the field to include those 0 values whilst utilising a sorted and shortened format for the month? This feels like a simple ask, but the solution is proving to be much more convoluted. I don't understand why there isn't an option with date hierarchies to have some format control for the specific level.

e.g.
2024(yyyy) vs 24(yy) for Year
Q1 vs Quarter 1 vs 1 for Quarters
January vs Jan vs 01 vs 1 for Months
1 vs 01 vs 1st for days. Etc.

[Anonymous · Accepted solution]

Hi amitchandak ,thanks for the quick reply, I'll add more.

Hi @SamIsh ,

Regarding your question, it is currently not possible to format the date hierarchy table automatically created by Power BI. You can manually create a date dimension table, filter data through the date table, and format it.

The Table data is shown below:

Use the following DAX expression to create a table

DateTable = ADDCOLUMNS(
    CALENDAR(DATE(YEAR(MIN('Table'[Date])),1,1),DATE(YEAR(MAX('Table'[Date])),12,31)),
    "Year",FORMAT([Date],"yy"),
    "Month",FORMAT([Date],"MMM"),
    "Quarter",FORMAT([Date],"Q\Q"),
    "Day",FORMAT([Date],"DD"
    ))

Use the following DAX expression to create a measure

Measure = 
IF( ISBLANK(COUNTROWS('Table')),0,COUNTROWS('Table'))

Final output

Best Regards,
Wenbin Zhou

[SamIsh]

Thanks for this answer, Wenbin! This is great.

I think it's 90% there. However, there is an issue with your sort rank_for_month column when you take into account multiple years of data. There can't be more than one value in 'rank_for_month' for the same value in 'Month'. As this graphic only looks at the current year, I adjusted your code to take the MAX year for the lower date values. (So it only looks from 1/1/YYYY(Current) - 31/12/YYYY(Current))

DateTable = ADDCOLUMNS(
    CALENDAR(DATE(YEAR(MAX('Table'[Date])),1,1),DATE(YEAR(MAX('Table'[Date])),12,31)),
    "Year",FORMAT([Date],"yy"),
    "Month",FORMAT([Date],"MMM"),
    "Quarter",FORMAT([Date],"Q\Q"),
    "Day",FORMAT([Date],"DD"
    ))

Just another adjustment to your answer that might help - your rank_for_month column calculation was missing ref:

rank_for_month = MONTH([Date]) + YEAR([Date]) * 100

Open to you if you want to try and fix this for any years but this works great for me! Appreciate it a lot! 

[Anonymous]

Hi @SamIsh ,

Oops, forgot to consider that. The expression for the calculated column should be simpler then.

rank_for_month = MONTH([Date])

If you want to take into account the year, Add filter here.

Best Regards,
Wenbin Zhou

[amitchandak]

@SamIsh , You can also try right-clicking on the field and selecting "Show items with no values" for all fields. This works best when the month is coming from a dimension or master table.

[SamIsh]

Hey Amish, 

As mentioned;

"This meant that I could populate 0 values into the matrix with "Show items with no data" and the calculated column below." - however, when swapping to the formatted date field this made no difference (and the 0 values disappeared.)