Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm pretty new to power bi and I'm having a little trouble figuring this out.
I've got a tabular model running on SSAS that I've connected to Power BI Desktop. One of my measures is a count of incidents. I want to plot the incidents on a column chart by month for the last 12 months. I've set up my chart as follows:
The Trailing _12_Mo_Filter is a field on my Calendar table that flags any dates that fall within the last 12 months.
My X-Axis is set as Continuous.
I've had two incidents in the last 12 months (Sep 15 and Oct 15), so I expected my chart would plot the data as:
Jul 15 0
Aug 15 0
Sep 15 1
Oct 15 1
Nov 15 0
Dec 15 0
Jan 16 0
Feb 16 0
Mar 16 0
Apr 16 0
May 16 0
Jun 16 0
Instead, I get a chart with the following
Sep 1 1
Sep 6 0
Sep 13 0
Sep 20 0
Sep 27 0
Oct 1 0
So I have two questions:
Thanks!
Solved! Go to Solution.
In this scenario, you can create another table which includes the date of last 12 months in Power BI. Then add a calculated column which converts the year and month to a number. So that you can use the converted 12 numbers as the X-Axis value. For more details, please refer to following steps:
YearMonthTable = DATESINPERIOD ( CalendarTable[Date], MAX ( CalendarTable[Date] ), -12, MONTH )
Year_Month = YEAR ( YearMonthTable[Date] ) * 100 + MONTH ( YearMonthTable[Date] )
Regards,
Simon Hou
Hi
1. Create new calculated Column
Column = FORMAT("Date Field","DDD") & " " & FORMAT("Date Field","YY") --- from this u will get like Jan 15,Feb 15
2. U need to enable the "Show item with no data " from Axis in your visual..
try this,
If you change the x - axis to categorical instead of continuous it may fix both of your problems. If it sorts your dates wrong then click on the little dots in the upper right hand of the chart and sort by months.
Does that solve it?
When I try setting the axis to categorical, I only get two data points (Sep 15 and Oct 15). In Excel there is an option to have a pivot table display rows with no data... is there similar functionality in Power BI? The problem is arising since the Incident_Count measure is null for all months except Sep 15 and Oct 15.
Thanks!
The only thing I could get to work was by inserting a calendar table and making a relationship to my dates in the transaction table, and then leaving the x - axis on continuous. It's not perfect, but it leaves blanks in some years with no data.
Your solution sounds similar to what I've already got unless I misunderstand.
I've using a tabular model for my data source. In my tabular model, I have a Calendar table and an Incidents table. The Incidents table is joined to the Calendar table on the date field. Is this different from what you described
I think we are setup the same. I am using a tabular model with a calendar table joined on the date field to my transactions table.
Interesting. Does your transaction table include records for the dates that show zero value on your chart? In my example, my incidents table only includes records for dates where an incident occurred. My current fix involves creating a new incidents view by crossjoining to the calendar table to ensure every date has a record in the table.
In this scenario, you can create another table which includes the date of last 12 months in Power BI. Then add a calculated column which converts the year and month to a number. So that you can use the converted 12 numbers as the X-Axis value. For more details, please refer to following steps:
YearMonthTable = DATESINPERIOD ( CalendarTable[Date], MAX ( CalendarTable[Date] ), -12, MONTH )
Year_Month = YEAR ( YearMonthTable[Date] ) * 100 + MONTH ( YearMonthTable[Date] )
Regards,
Simon Hou
I followed a similar solution, but I did all the data wrangling in SQL and fed it to my tabular model in SSAS. Thanks for pointing on the "Show Items With No Data" checkbox. That is what I was originally looking for; it is somewhat hidden in the Power BI interface.
Let me know my answer is not solve your issue,
Chears !!!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |