Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |