Stacked column chart - Want to fill missing X-Axis Date gaps BUT within relative date range filter

[Ptown]

Hi,

I'm having a problem in Power BI Desktop when trying to create a stacked column chart that has a relative date filter showing the next 12 months.

I have a number of opportunities which each have an associated "Value" and a predicted "Win date". I would like my stacked column chart to show the next 12 months on the X axis (regardless of whether there are any opportunities in any given month). 

If I do not tick "Show items with no data" then the X axis doesn't show the following 12 months. It only shows months that have an opportunity in.

If I do tick "Show items with no data" then the X axis shows much more than the following 12 months.

I have read countless posts outlining the same or similar problems. I understand exactly what is happening and why.  My question is: What is a simple and effective way to acheive my desired output? 

I have created a sample file with three tabs. Two of them show the problem. The third outlines my desired output.

Sample file 

Thank you in advance for any help!

[BA_Pete]

Hi @Ptown ,

First, create your value measure like this:

_newValue = SUM(Opportunities[Value]) + 0

Then set up a Calendar table. You may be able to do this using CALENDARAUTO(), but with the limited dataset you provided this created a circular reference when relating to the fact table.

I actually used this in Power Query:

let
	    Source = { Number.From(List.Min(Opportunities[Win Date]))..Number.From(List.Max(Opportunities[Win Date])) },
    convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    chgDateType = Table.TransformColumnTypes(convToTable,{{"Column1", type date}}),
    renCols = Table.RenameColumns(chgDateType,{{"Column1", "date"}})
in
    renCols

Relate your Calendar table to your fact table as follows:

Calendar[Date] (One) to Opportunities[Win Date] (Many)

Use Calendar[Date] for your axis and your relative date slicer, and your new measure for the values.

Do NOT check 'Show items with no data'.

This gives me the following output:

Pete

[Ptown]

Hi @BA_Pete  thank you for your help with my question,

That method gets me closer to what I want but not quite. I need to have space on the x axis for exactly 12 stacked columns, one for each month. Your output appears to have a bar for each day.

For the Calendar[Date] I tried selecting 'Date Hierachy' in the 'Axis' dropdown box. But then the axis once again displayed all the dates past and future.

Any further help towards my required output greatly appreciated.

[BA_Pete · Accepted solution]

Hi @Ptown ,

Just change the chart axis from Calendar[date] to Calendar[month] or Calendar[monthYear].

If you use this calendar, it already includes these fields:

let
	    Source = { Number.From(List.Min(Opportunities[Win Date]))..Number.From(List.Max(Opportunities[Win Date])) },
    convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    chgDateType = Table.TransformColumnTypes(convToTable,{{"Column1", type date}}),
    renCols = Table.RenameColumns(chgDateType,{{"Column1", "date"}}),
    addFinYear = Table.AddColumn(renCols, "finYear", each Date.Year([date]+#duration(275,0,0,0))),
    addRelativeFY = Table.AddColumn(addFinYear, "relativeFY", each [finYear] - Date.Year(DateTime.LocalNow()+#duration(275,0,0,0))),
    addFinPeriod = Table.AddColumn(addRelativeFY, "finPeriod", each if Date.Month([date]) >=4 then Date.Month([date])-3 else Date.Month([date])+9),
    addFinYearPeriod = Table.AddColumn(addFinPeriod, "finYearPeriod", each [finYear]*100+[finPeriod]),
    addFinHY = Table.AddColumn(addFinYearPeriod, "finHY", each if [finPeriod] >= 1 and [finPeriod] <= 6 then "H1" else "H2"),
    addFinQtr = Table.AddColumn(addFinHY, "finQtr", each if [finPeriod] >= 1 and [finPeriod] <= 3 then "Q1"
else if [finPeriod] >= 4 and [finPeriod] <= 6 then "Q2"
else if [finPeriod] >= 7 and [finPeriod] <= 9 then "Q3"
else "Q4"),
    addMonth = Table.AddColumn(addFinQtr, "month", each Text.Start(Date.MonthName([date]),3)),
    addMonthYear = Table.AddColumn(addMonth, "monthYear", each Text.Combine({[month], Text.End(Text.From(Date.Year
([date])),2)}, " ")),
    addDayKey = Table.AddColumn(addMonthYear, "dayKey", each Date.DayOfWeek([date])),
    addDay = Table.AddColumn(addDayKey, "day", each Text.Start(Date.DayOfWeekName([date]), 3)),
    chgAllTypes = Table.TransformColumnTypes(addDay,{{"date", type date}, {"finYear", type text}, {"finPeriod", Int64.Type}, {"finYearPeriod", Int64.Type}, {"finHY", type text}, {"finQtr", type text}, {"month", type text}, {"relativeFY", type text}, {"monthYear", type text}, {"dayKey", Int64.Type}, {"day", type text}}),
    #"Sorted Rows" = Table.Sort(chgAllTypes,{{"date", Order.Descending}})
in
    #"Sorted Rows"

In the data view of the Calendar table, you will need to select the [monthYear] column, then go to 'Sort by column' on the ribbon and select [finYearPeriod] from the dropdown list. This will allow you to sort your months in calendar order, rather than alphabetical.

This gives me the following output:

As a side note: once you have your Calendar table in place, you should go into the report Options & Settings and switch of Auto Time Intelligence. Then right-click your Calendar table in the fields list and choose 'Mark as date table'. This will assist you in utilising the in-built Time-intel functions, as well as save you a lot of model size as your model grows.

Pete

[Ptown]

Thank you Pete!

That works in my sample data and I think I should be able to now apply it to my real data model.