Forum Discussion

MyThumbsClick's avatar
MyThumbsClick
Helper II
2 years ago
Solved

Count by Month - Line Graph Not Showing Zero Values

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-axi...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

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's avatar
MyThumbsClick
Helper II
2 years ago

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

 

Greatly appreciated! 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    You are welcome.