Using Dates on X-Axis

[Budfudder]

I have a very simple table - a number of date/times from 2012 to the present, with, for each date/time, the details of a particular item created that is relevant to our business. I want to turn that into a table which shows each date from 2012 to the present with, for each date, the number of items created on that date - I don't care about the time.

So what I have is (example):

9/5/2012 2:14:13 PM Item 1 detail

9/5/2012 6:57:22 PM Item 2 detail

9/7/2012 1:01:19 AM Item 3 detail

And what I want is (example):

9/5/2012 - 2

9/6/2012 - 0

9/7/2012 - 1

So I first changed the datatype of the column containing the date/time to just a date - great, that worked find. I used a Count to get the number of items created on each day - great again.

In the Visualizations tab, I'm able to choose whether I want to use the date as just a date or as a Date Heirarchy - year, month, day:

If I choose to use it as just a date - it doesn't show dates with no items created, even though I tick 'Show items with no data':

If I choose to use it as a Date Heirarchy, it correctly shows those dates on which no items were created:

but when I try to graph it (using, for example, a Line chart) it shows the graph at the top level with each year, and when I drill down it shows 12 points along the x-axis. So on the '3' mark on the x-axis, it's actually adding all of the occurences in March of any of the years:

When I drill down further, I get 31 points along the x-axis, and each of those is showing the number of occurences on that day of ANY month - so at the '5' mark on the x-axis, it's showing the number of items created on the 5th of any month.

How do I get it to show, at the lowest level of the chart, the number of items created on that date (including zeros)?

[Anonymous]

Hi @Budfudder,

In your scenario, you can firstly create a date table using the following formula, then create relationship between date table and your original table using date fields.

Table = CALENDAR(DATE(2012,1,1),DATE(2016,12,31))

Secondly, create a measure in your original table using the formula below.

Measure = COUNTA(Table1[item])+0

Thirdly, create visuals using date field from date table and the measure in your original table. For more details, please review the attached PBIX file.

Thanks,
Lydia Zhang