Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a set of data where issues/calls are logged into a spreadsheet. I want to show a line graph over the entire time span of the data which is approx 3 years. My go-to method for something like this is to create a Date Table using the following steps, inspiration for this was taken from here: https://community.powerbi.com/t5/Desktop/How-to-show-2-years-on-x-axis-of-bar-chart-when-displaying/... and has worked well for me for 18 months now:
1) DateTable = CALENDAR(DATE(2018,1,1),DATE(2030,12,31))
2) YearMonthNumber = (Year(DateTable[Date])) * 12 + Month( DateTable[Date])
3) MonthYear = Format(DateTable[Date],"mmm") & "-" & Format(DateTable[Date],"yyyy")
4) Sort MonthYear by YearMonthNumber
Then I link a column from the data which is a Date type, in this case "Issue Logged On" to DateTable[Date], then I would insert a line graph, put DateTable[MonthYear] on the X-Axis and a column from the Data in this case "Call No." as the Y-Axis set to "Count of".
This time though the line graph simply shows on the X Axis "Blank" and the Y-Axis just displays the total number of issues.
I've done this hundreds of times before with similar data with no issues but cannot fathom why the X-Axis is showing "Blank".
Thanks
Solved! Go to Solution.
Looks like the relationship is not working.
Maybe "Issue Logged On" has a time portion in it which prevents matching with the date table?
You can test this by removing DateTable[MonthYear] from the x-axis and putting "Issue Logged On" there instead. Do you get a reasonable-looking chart?
If so, remove "Issue Logged On" from x-axis and put the DateTable[Date] there. Does it look the same?
Looks like the relationship is not working.
Maybe "Issue Logged On" has a time portion in it which prevents matching with the date table?
You can test this by removing DateTable[MonthYear] from the x-axis and putting "Issue Logged On" there instead. Do you get a reasonable-looking chart?
If so, remove "Issue Logged On" from x-axis and put the DateTable[Date] there. Does it look the same?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |