Forecasting with irregular dates

[BUK2020]

I am trying to forecast, but recieve the message that my data is too irregular to do so.

Having looked at past solutions i can see that this link shows how to fix the issue: http://www.erikhudzik.com/2017/01/16/power-bi-forecasting-feature-and-when-your-data-is-too-irregula...

However, my date table (with continuous dates), which is linked via a relationship with my invoice order date in my sales table, is not a query, it is simply a table i created via trhe new table function in the modeling tab.

Is there any other way to get around this issue? Possibly turning my date table into a query? Or merging in another way?

....

[Anonymous]

When I modelled Rate as a function of Interval I obtained the following. Using a prediction of INTERVAL using it's past this equation can then predict rate , which can the be used to predict demand. This kind of model allows autoregressive structure in rate to be incorporated as well as allowing for Pulses , Level Shifts and/or Local Time Trends in rate.

MODEL COMPONENT LAG COEFF STANDARD P T
# (BOP) ERROR VALUE VALUE

Differencing 1
1CONSTANT .295 .840E-01 .0246 3.51
INPUT SERIES X1 INTERVAL

Differencing 1
2Omega (input) -Factor # 1 0 .685E-01 .346E-01 .1193 1.98
INPUT SERIES X2 I~P00002 12/03/11 PULSE

Differencing 1
3Omega (input) -Factor # 2 0 1.43 .168 .0010 8.52
INPUT SERIES X3 I~P00007 12/08/11 PULSE

Differencing 1
4Omega (input) -Factor # 3 0 -.935 .168 .0051 -5.57
INPUT SERIES X4 I~P00010 12/11/11 PULSE

Differencing 1
5Omega (input) -Factor # 4 0 1.37 .260 .0062 5.27

[Greg_Deckler]

So, you could potentially create the merge table in DAX using something like NATURALINNERJOIN, GENERATE, etc.

[BUK2020]

could you provide some more info on this please?

[ibarrau]

Hi, the calendar or date table is mandatory to time intelligence or this kind of calculations. It is a very good modeling practise too. There are three places where you can create this table like this post shows:

https://blog.ladataweb.com.ar/post/185012464051/data-modeling-como-hago-una-tabla-fecha

It depends on your environment to know where to run it. You should use it, you can't avoid it. With your Date Table related with your other tables you have to use always the date columns of the data table for calculations and visualizations.

Hope this helps,

[BUK2020]

It seems i am using something similar to the last mode described in the link you provided.

My date table was constructed using DAX: 

Date = CALENDAR(FIRSTDATE(ST030100[INVOICE ORDER DATE]),LASTDATE(ST030100[INVOICE ORDER DATE]))

The problem i have is that i cannot merge my data table to the ST030100 table as my date table is not a query, it is simply a table created inside Power BI from scratch, using the DAX formula shown above.

[ibarrau]

Just relate the tables together in the relationshops view by Date. Then use the dates columns of date table on the visualizations and the numbers of your table to build this situations.

[BUK2020]

I tried this, however it doesn't seem to work.

I have connected my date table to my sales table - joining the date fields. so in theory when i add the date value to a line chart it should show every date from the past two years, but it doesnt, hence why my forecasting isnt working.

Is there any reason this wouldnt be working? 

[BUK2020]

This is strange...

So i added my date (from date table) and revenue into a visuals table, then made sure the table was showing items with no data. Then i converted this to a line chart, which still has "show items with no data" checked, but on the chart itself it is excluding all dates with no data.

Is there any reason for this? as far as i can see it should be showing all items.