Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count when date is missing equal to 0

Hello,

 

Hope you are doing well.

 

It's my first time here.

 

So, I have a table like that called "Example"

 

 

 

And i would like to count the number of B for each day between the min date and the max date (so here between 01/01/2000 and 07/01/2000 but it must work for future date like 08/01/2000 etc...)

So for the missing date 02/01/2000 the count of "B" should be 0, for 01/01/2000 and 06/01/2000 should be 0 as well.

 

I got this line chart :

 

But i want to obtain this line chart :

Hope you can help me.

 

Thanks in advance!

 

 

 

 

  • This seems to work. 

    Create a calendar table (I've aslo created a dimension table for Type which is recommended)

    Use the field(s) from the calendar table for the axis and use this measure:

    Count Type =
    COUNT ( 'Type Table'[Type] ) + 0
    

     

     

6 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion

    Try:
    Count B = CALCULATE(COUNT(Table[type]), Table [type] = "B")

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hello PaulDBrown,


      Unfortunatley, it doesn't work. I got the same output.

      By the way, I want it to work with the slicer so can be either A or B not only B.

      Thanks for your answer anyway.🙂


      • amitchandak's avatar
        amitchandak
        Super User

        Anonymous , add +0 to measure and try axis type categorical if needed