cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pe2950 Frequent Visitor
Frequent Visitor

Counting Rows Across Relative Dates, Handling Dates with 0 items?

I'm trying to generate some reports based off an appointment table.

 

1 row in the table represents an appointment. The create timestamp of the appointment is linked to a dateKey table.

 

I have a "count" row in appointment table with the value static at 1; when I'm trying to determine number of appointments scheduled on a given day I just sum all the values of count. 

 

Now, here is where my problem occurs. I have a column chart where I'd like to display "number of appointments created, by day, for the last 7 days"

 

In the report, I have DateKey[Date] as the Axis, and Count as the Value. Then the filter is relative to last 7 days. This works out just fine, except for days where 0 appointments were created. Instead of the count of the rows being 0, the visual just sort of skips the date. This becomes a problem if I try to use a line chart, as instead of graphing down to 0 and then back up, it will skip that date and draw a line between the two non zero values. 

 

Can anyone offer some input on this?

 

 

2 REPLIES 2
Super User
Super User

Re: Counting Rows Across Relative Dates, Handling Dates with 0 items?

Hi @pe2950,

 

On the X-Axis option try to change the axis type from Categorical to continuous should give what you need, that is to show the dates without values.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Community Support Team
Community Support Team

Re: Counting Rows Across Relative Dates, Handling Dates with 0 items?

Hi @pe2950,

 

Firstly, please change the X-axis type from Categorical to Continuous. Secondly, add below measure to chart value instead of dragging the [Count] column into chart.

Measure = SUM(AppointmentTable[Count])+0

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.