Forecasting using last four weeks as an average - figures not calculating correctly?

DAX DAX Calculations
1

Hello EDNA Forum,

I’m new to forecasting and I have been reading and watching lots of tutorials and now I bit confused, I was wondering if anyone can help.

I’m trying to create my forecast by looking averaging the last four weeks sales.

image

I had download the DATEADD function tutorial to try and work out the logic I required.

DAX formula that I have written is:

Sales Forecast - average of last four weeks = 
CALCULATE (
    AVERAGEX ( VALUES ( Dates[Date] ), [Total Sales] ),
    DATEADD ( Dates[Date], -1, MONTH )
)

However the result is not returning the correct figure when I slice the data by week, Is the above formula correct?

image
image963×542 45.2 KB

I would ideally like to create my forecast and then compare actuals to forecast each week, but my figures are not summing correctly

image

Any assitance would be greatly appreciated :grinning:

Thank you EDNA
The DATEADD Function .pbix (572.2 KB)
exported data.xlsx (132.3 KB)

1 Like
2

Hi @Natty, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

image

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

3

Hi @Natty - I will first suggest to understand how to calculate Weekly information. DAX provided and used in PBIX will not work. Below are few tutorials that will help you.

Similarly if you Search “Week Calculations” in Forum, you will get multiple results on how to calculate Weekly transactions.

https://forum.enterprisedna.co/search?q=week%20calculation

Thanks
Ankit J

4

Hi @Natty, did the response provided by @ankit help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

5

Thank you @ankit for providing this information, I will definately take a look.

Have a great day

Kind Regards

Natty