Count by Month - Line Graph Not Showing Zero Values

[MyThumbsClick]

Hi All

I have a Projects table - example below:

Project_ID	Go_Live_Date
1	01/01/2024
2	12/01/2024
3	11/03/2024
4	01/04/2024

I have a line graph visulation with:

• X-axis - Go_Live_Date (date hierarchy - Year, Month)
• Y-axis - Count of Project_ID

This works fine and shows total projects per month except its not showing a zero value for Feb when there is no project with a Go_Live_Date. Do I need to create a seperate Count measure and use some clever DAX?

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach.

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number
2. Create a relationship (Many to One and Single) from the Go_Live_Date column to the Date column of the Calendar Table
3. to your visual, drag Year and Month name from the Calendar Table
4. Write this measure

Measure = coalesce(distinctcount(AllProjects[ID]),0)

Hope this helps.

[MyThumbsClick]

Hi Ashish

I do have 1 more query if thats ok? I would like to add a filter to the measure dependant on another column value. Something like this: 

measure = CALCULATE(coalesce(distinctcount(AllProjects[id]),FILTER(AllProjects,[m_a] = "True"),0))

However, im getting the following error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Is there a way to achieve this?

Thanks

[Ashish_Mathur]

Hi,

Does this measure work?

Measure = coalesce(calculate(distinctcount(AllProjects[id]),AllProjects[m_a] = "True"),0)

If TRUE is a boolean value, then remove the double quotes.

Hope this helps.

[MyThumbsClick]

Thats worked perfectly. Thank you

[Ashish_Mathur]

You are welcome.

[MyThumbsClick]

Thanks. With your instructions (and a bit of fiddling with the sort), the graph is visualizing as i want it. 

Greatly appreciated! 

[Ashish_Mathur]

You are welcome.

[Kishore_KVN]

Hello @MyThumbsClick ,

Yes you have to create separate measure for the count and after creating measure at the end of the measure please mention '+0' so that if the value is blank it will show '0'

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

[MyThumbsClick]

Thanks Kishore_KVN

I created the below basic measure which does the trick and shows a zero value for where there is no count.
projects_per_month_count = DISTINCTCOUNT(AllProjects[id])+0

The problem im having is that i want to display the graph with a relative date filter - ''Is in the next 12 months". So Jan - March should be for months in 2025 and should appear after December but they are showing as if in normal calendar year. I have a feeling i cant achieve what im after with just a measure?