Forecasting considering end date of project & 3 months of historical / moving average

[OKgo]

pbix file

https://www.dropbox.com/s/a5ce0vex9jas0sh/PowerBi%20Timesheet%202.pbix?dl=0

Unsure if I have the correct relationships activate.

The ask

[Ashish_Mathur]

Hi,

Your question is not clear.

[anushaghi123]

Hi,  Need help with the following question 

Each product has data, for that we have to create data as a moving average of the last 4 months

For example: United States 37101 April, May, June, July are 1683,1668,776,1885. Predict Aug as Average of the 4, then use average of May, June July, Aug as September.  And a slider that can be used to do -20% to +20%.  Where we cn check forecast  in case we go -1% of the moving average or 5% of the moving average. I have created a slider using New Parameter 

Dax Formula (Quick Measure) - Product_Count rolling average =
IF(
ISFILTERED('Table_name'[dimdate]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Table_name'[dimdate].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Table_name'[dimdate].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, 'Moving Average'[Moving Average Value], MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Table_name'),
'Table_name'[dimdate].[Year],
'Table_name'[dimdate].[QuarterNo],
'Table_name'[dimdate].[Quarter],
'Table_name'[dimdate].[MonthNo],
'Table_name'[dimdate].[Month]
),
__DATE_PERIOD
),
CALCULATE(
SUM('Table_name'[Product_Count]),
ALL('Table_name'[dimdate].[Day])
)
)
)

Could you please help.

Thanks

[Ashish_Mathur]

Hi,

Your question is not clear at all.  Share data in a format that can be pasted in an MS Excel file and show the expected result very clearly.

[anushaghi123]

Hi @Ashish_Mathur  ,

Can't share the data.

Please help me with the following issue

I am getting column total error and taking Matrix table 

Rows - Country,ProductCategory

Column - Month

I'm getting column total incorrect for Forecast(Blue) data.

Dax Formula (Quick Measure) - Product_Count rolling average =
IF(
ISFILTERED('Table_name'[dimdate]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Table_name'[dimdate].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Table_name'[dimdate].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, 'Moving Average'[Moving Average Value], MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Table_name'),
'Table_name'[dimdate].[Year],
'Table_name'[dimdate].[QuarterNo],
'Table_name'[dimdate].[Quarter],
'Table_name'[dimdate].[MonthNo],
'Table_name'[dimdate].[Month]
),
__DATE_PERIOD
),
CALCULATE(
SUM('Table_name'[Product_Count]),
ALL('Table_name'[dimdate].[Day])
)
)
)

Thanks

[Ashish_Mathur]

I cannot help without seeing the file and the expected result.

[anushaghi123]

Thanks for quick reply @Ashish_Mathur. Will try to create one sample file.

[OKgo]

In Excel the yellow area could be a pivot table. Then the green/red are could be an AVERAGE formula. Or even better an array formula that VLOOKUPs the project end date and return a zero when the project is over. The project end dates are stored in another table within the pbix file. 

[Ashish_Mathur · Accepted solution]

Hi,

See if this works.  Download the file from here.

[OKgo]

Hi Ashish. This is wonderful. I am going to study the logic here and study each and every DAX function watching youtube videos on them. I have this now working in my live model and its great!!!

[Ashish_Mathur]

Thank you.  Glad to hear that.