Unable to get forecasting by month not day

[locka]

Hi everyone I have an issue with Power bi line chart and forecasting.   I cannot get it to forecast the next 4 months with only month date points.  Instead Bi wants to forecast with daily points.

Little background to the problem
I have a new dataset replacing an old one linked to our previous ITSM tool.  As part of this new dataset we decided to create our datetables within SQL and load them in rather than use dax code as we had been doing(before see code sample below).   I have also turned off all Automatic time intelligence to stop file being bloated out.  All dates are all imported ok and each of  the 4 date tables are marked as date table in Powerbi.   I have also setup manual Hierarchy in each datetable as follows:-
HierarchyYear

HierarchyMonth

HierarchyDay 

Each ones of these is a copy of the intial date field and comes in as date format.  I have then formatted the columns as follows: -

yyyy
   MMM
     dd

If I have basic line chart which is a count of tickets on Y axis and HierarchyMonths on X axis Bi wants to display daily.  With my old dataset it would show grouped as monthly counts not daily.    If I add in the (below code sample) to the new dataset and create my old date Hierarchy it works fine. and groups over a month and not a day. (See picture) 


I suspect this is due to the fact that other Hierarchy used is based on
Date

MonthNameShort_year

Where the X axis is MonthNameShort_year I have tried to replicate format whichs is: -

YYYY-MMM

In my new table with formatting of the column to match.  However this does not seem make any difference.  I've also tried doing formatting in Powerquery as part of the import but unfortunately it just turns those fields in to text or numbers which does not work for forecasting.  

IV_dim_CALENDAR_CreatedDate = ADDCOLUMNS (

CALENDAR( MIN(FACT_Faults[CreatedDateTimeDay]) , DATE(year(today()),12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"WeekStart",[Date]-WEEKDAY([Date],2),
"WeekEnd",[Date]+7-WEEKDAY([Date]),
"Year", YEAR ( [Date] ),
"FiscalYear", if(Month([Date])<4,Year([Date])-1,Year([Date])),
"Monthnumber", FORMAT ( [Date], "MM" ),
"Day", DAY ( [Date] ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YYYY-MM-DD", FORMAT ( [Date], "YYYY-MM-DD" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameShort_year", FORMAT ( [Date], "YYYY-mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"MonthDay", FORMAT ( [Date], "YYYY-MM-DD" ),
"Weeknumber", WEEKNUM ( [DATE],2 ),
"DayOfWeekNumber", WEEKDAY ( [Date], 2 ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q"
& FORMAT ( [Date], "Q" )
)

My apologies I'm unable to add a pbix of the data to allow you to take a look

[locka · Accepted solution]

After further invistigation.  I have yet to get the Hierarchy to work properly.  However Have managed to at least get it to forecast grouped in months by using following formated column as date YYYY-MMM.  This was changed within the table view and formating a column in this date format.

[_AAndrade]

Hi @locka,

I suggest you check two things:

- Mark the new date table as Data Table

- Check what you have on the type of X-Axis. You need to set continuous

[locka]

Thanks for your response, as mentiomed table is already marked as data table. 

Also mentioned I get forecasting but in days not month. So it has to be continous for it to work. 

[rajendraongole1]

Hi @locka -you can configure the Under the X-Axis, check whether Type is set to Continuous (not Categorical) and is using the actual date type (not text).

Format your Year-Month as a Date, not text.Use Power BI’s built-in date hierarchy to check if it resolves the issue.Ensure Forecast settings are correctly set to forecast monthly values, not daily.in your line chart visual.In the Analytics pane (the magnifying glass icon in Visualizations), choose Forecast.In the Forecast length, set it to forecast for the next 4 months (or whichever period you want).Adjust the confidence intervals and seasonality based on your data patterns, ensuring that you are forecasting at the monthly level, not daily.

Hope by adjusting the above changes it works as expected.

[locka]

Hi thanks for your response.  All the format of the chart and columns are set correctly. As it does forecast ok but not monthly only daily.

My forecast settings are as follows(as we don't have enough data to bases on a year) 

Units: monthly

Length: 4
Ignore the last: 1

Seasonality: 6

Confidence intervals: 95%

haven't tried turning on automatic date/time intelligence as i didn't want to use it.   So I can turn it on to test.  However if that works im not sure what that solves as I don't want to have it turned on.