Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AUaero
Responsive Resident
Responsive Resident

Plot incidents versus time, months with no data

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:

 

  • Axis = Month
  • Values = Incidents
  • Filters = Trailing_12_Mo_Filter

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:

 

  1. How can I force Power BI to plot months with no data?
  2. How can I force Power BI to display the X-axis labels as months rather than interpolating dates?

Thanks! 

1 ACCEPTED SOLUTION

@AUaero

 

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:

 

  1. Create a new date table for last 12 months.
    YearMonthTable =
    DATESINPERIOD ( CalendarTable[Date], MAX ( CalendarTable[Date] ), -12, MONTH )
    

  2. Add a calculated column which represents the year and month.
    Year_Month =
    YEAR ( YearMonthTable[Date] ) * 100
    + MONTH ( YearMonthTable[Date] )
    
    2323.png
  3. The relationship between Incidents table and the new created table will like below:
    232323.png
  4. Drag values into the column chart. Set X-Axis type to “Categorical” and select “Show items with no data” (this force Power BI to plot months with no data) as below:
    121212.png

 

Regards,

Simon Hou

View solution in original post

10 REPLIES 10
Baskar
Resident Rockstar
Resident Rockstar

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,

Beckham
Advocate II
Advocate II

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?

AUaero
Responsive Resident
Responsive Resident

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.

 

 Blank Dates.PNG

AUaero
Responsive Resident
Responsive Resident

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. 

AUaero
Responsive Resident
Responsive Resident

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.

@AUaero

 

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:

 

  1. Create a new date table for last 12 months.
    YearMonthTable =
    DATESINPERIOD ( CalendarTable[Date], MAX ( CalendarTable[Date] ), -12, MONTH )
    

  2. Add a calculated column which represents the year and month.
    Year_Month =
    YEAR ( YearMonthTable[Date] ) * 100
    + MONTH ( YearMonthTable[Date] )
    
    2323.png
  3. The relationship between Incidents table and the new created table will like below:
    232323.png
  4. Drag values into the column chart. Set X-Axis type to “Categorical” and select “Show items with no data” (this force Power BI to plot months with no data) as below:
    121212.png

 

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.

 

Baskar
Resident Rockstar
Resident Rockstar

Let me know my answer is not solve your issue,

 

Chears !!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.