Column Chart and unwanted years and months with +0 on count measure

[bdehning]

I use a Year Text field and Month Text field in X -Asis and I use a count measure for Y Axis.   I have set a Date field filter for Relative Date and it is in the last 5 years.  

Everyting works great except I can not see months with a zero count. 

If I add a +0 to my Count Measure, my chart shows years and months going back to 1963. all with 0 as it is all outside the 5 year filter range I want. 

How can I address this issue?  

[Anonymous · Accepted solution]

Hi @bdehning ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a table.

CALENDAR Table = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

(3) We can create a measure. 

Flag = IF(YEAR(MAX('CALENDAR Table'[Date]))<=YEAR(TODAY()) &&  YEAR(MAX('CALENDAR Table'[Date]))>=YEAR(TODAY())-4 ,1,0)

(4) Place the date field of the calendar table on the x-axis with "show items with no data" checked and [Flag=1] on the visual object filter.

Best Regards,

Neeko Tang

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

[AnalyticPulse]

You can create a separare calender table and try using count  measure with "all" filter to to meet specified criteria.
CountWithZeros = CALCULATE( COUNT('Table'[Column]), ALL('Table'), VALUES('DateTable'[Date]) ) 

your chart should display all months within the selected date range, even if they have zero counts. The 'DateTable' ensures that all combinations of months and years are considered, and the relationship between tables ensures that the date filter is applied correctly.

If this helped, Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
https://instagram.com/analytic_pulse
https://analyticpulse.blogspot.com/

[bdehning]

I keep getting The All Function expects a table reference for argument 2, but a table expression was used.  

To clarify  -   CountWithZeros = CALCULATE( COUNT('Table'[Column]), ALL('Table'), VALUES('DateTable'[Date]) ) 

Which table and Column for the Count?  I use 

All Table?   I assume Calendar Table

DateTable[Date]?  I assume Calendar Date[Date]

[bdehning]

Ok I got the measure to take but I tried it as a filter and no zero months appear.  

Here is what I used  

CountWithZeros = CALCULATE( COUNT('LossRun'[Total Gross Incurred]), ALL('LossRun'), VALUES('CalendarTable'[Dates]) )

LossRun is the table where Total Gross Incurred resides

CalendarTable has the Dates Field to fill next to All?

I used LossRun table for ALL

Is that correct?