Hi,
How can i forecast for the next month (August) using this amount of data. In the nutshell this is what i want to achive
. Please i need you help on this and it will be nice if the solution is prof erred in DAX.Hello,
I just joined the Enterprise DNA Membership and I am really new to using this forum and I’m not even sure still where to go to post a question asking for help with a Dax.
Can someone please tell me if I am in the correct spot? I don’t want to post in the incorrect spot. I read all the rules but it’s very confusing. Thank you kindly.
Hi @joanne.osborne,
Welcome to the Forum!
Use (1) and (2) to search within the forum and/or locate relevant content across eDNA
Use (3) to create a new topic if you were unable to find helpful related content. Check the information in the Banners on top “How to use…” & “Asking Q…” so you know what’s appropriate and you’ve made sure you provided all details to have your question answered in a timely manner.
You may also find watching this video useful.
I hope this is helpful.
Hi @bafuzie,
Thanks for providing a PBIX file, I just had a look at it and have a few questions.
You don’t have a forecast table in your model, however I did find this forecast measure:
Forcast =
CALCULATE(SUM('NNPC Sales (2)'[Sales]),DATEADD('NNPC Sales'[Sales Order date],-1,MONTH))
Do you want last months sales as forecast for the next month?
Thanks.
Hi @Melissa, yes using last months sales to determine next month sales. And yes i tried to do some forecasting but it didn’t work as i wanted it to.
Hello
I hope I’m in the right spot to post a question about a DAX formula. I just joined Enterprise DNA and it’s a bit overwhelming. I only just started my Power BI journey a few months ago.
I am trying to post a question on how to just count up the number of days for this Concurrent Activities under Date Time and I could try count it manually but I’m hoping that I can do this with Dax. I tried to put this measure but it didn’t work.
Max concurrent = MAXX(DateTime2,[Concurrent Hours]) and just changed it to Count concurrent = COUNTX(DateTime2,[Concurrent Hours])
but COUNTX didn’t count anything up. It seems like it would be a simple change but it didn’t count up the number of days for these concurrent activities
Okay @bafuzie so give this a go.
Forcast v2 =
VAR LastSales = MAX( 'NNPC Sales'[Sales Order date] )
VAR LastDay = CALCULATE( STARTOFMONTH('Date'[Date] ), FILTER( ALL( 'Date' ), 'Date'[Date] = LastSales ))-1
VAR FirstDay = DATE( YEAR( LastDay ), MONTH( LastDay ), 1 )
RETURN
CALCULATE( SUM( 'NNPC Sales'[Sales] ),
DATESBETWEEN( 'Date'[Date], FirstDay, LastDay )
)
It calculates the last day of the previous month, constructed a start of month date as well and used that to calculate the sum of sales between these 2 dates.
I hope this is helpful.
Hi @joanne.osborne,
At the moment you are hijacking someone else’s thread - read my previous post addressed to you (#3) on how to create a new topic for your question and also include a PBIX file - thanks!
In addition to @Melissa’s approach, there’s another tack you can take here, which is to use Power BI’s built-in time series forecasting capabilities:
This uses a pretty sophisticated exponential smoothing method that can adjust for seasonality in timeseries data. There are some limitations as to when you can use it (e.g., you need to have sufficient amount of data, and data need to be contiguous for the granularity selected, etc.), but I think this would be worth examining for your requirements.
Finally, here are a couple of videos from @sam.mmckay on forecasting that use yet a different approach.
I hope this is helpful.
Hi @Melissa,
Thanks for your effort, help me understand this, so my intention is to forecast for example the sales for August.
The calculation yah is a one period lag yes. my intention is to forecast for the month of August (a period ahead). i don’t know if i make sense yah.
Hi @BrianJ,
Thanks for this, i am aware of this picture method but it cant be translated into card format, i believe power bi as not provided a method of changing that into tables.
Okay sure but won’t that be equal to the current month’s Sales?
> Thanks for this, i am aware of this picture method but it cant be translated into card format, i believe power bi as not provided a method of changing that into tables.
Yes and no. I don’t believe that there’s a way to access the forecasted data completely dynamically, but you can access it in the following way and use it in cards, visuals, etc.
When you run the forecast analytics, it gives you the option to “show as table”. If you do so, you will see three new created columns at the bottom of the table - one with the forecast values, and the others with the upper and lower confidence band limits. You can now export that info to CSV and then re-pull it into your Power BI report via Power Query. Once you do that, the forecast data just becomes regular columnar data that you can use in other analyses, visuals, etc. It would be ideal if you could call this forecast data dynamically (say, in a virtual table), but I don’t believe that’s currently possible. Regardless, the technique described above and pictured below may make the analytics pane approach usable for your requirement.
Hi @bafuzie, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
