Hello EnterpriseDNA World,
First off, this is my first post here, but I’ve been doing lots of reading and watching! Thanks for all the great content.
I’m really struggling how to wrap my mind around setting up my model my right.
Most reporting that I do is comparing our next 12 month forecast to historical sales. And this is an actual forecast from our forecasting software, not a DAX calculated forecast from historical sales (but i will be making a DAX forecast as well! hopefully).
I need to split out historical sales - I’d call actuals anything before the 1st of the current month, current MTD, and then the next 12 months. Sometimes we get sales order in advance - like a blanket order to cover future periods. I do need to evaluate these, but I don’t want to call them actuals. It would be more compare it to the future forecast.
Should I just have an all encompassing date table, and then add calculated columns of isActualPeriod, isFutureForecastPeriod, is FutureOrderPeriod?
This is an excellent question, and even experienced Power BI users may have very different answers and strategies in response to your question. Here’s how I would handle it:
I would absolutely start with Enterprise DNA Expert @Melissa’s incredible Extended Date Table. This will have the vast majority of fields you will ever need, including one called ISAFTERTODAY which can be used to easily separate historical data from future forecasts.
Melissa and I did a whole series of videos on time intelligence uses the extended date table that you can find on the portal site…In particular focus on the initial video, the one on offsets and the one on ISAFTERTODAY.
Generally, calculated columns are the least preferred choice for adding additional capabilities to date table – if there are columns you need regularly that are not included, best to modify the M code that creates the date table to add those (we can help you with that if needed). Otherwise, just build the conditions into the different measures you will use in your analysis.
Brian,
This is very helpful, and you make it sound simple!
That is wonderful… I’ve been pulling a date table from SQL, but think I am finally going to go ahead and make the jump to Melissa’s date table to utilize ISAFTERTODAY.
Once you get used to using it, it really is simple. It’s amazing how easy it makes time intelligence calculations. Other than DATEADD, DATEDIIFF, DATE and the EO series, I almost never need to use any of the myriad of canned time intelligence functions.
So this is just a filter I’d grab in my measures and calculations, or would I use it as a filter on visuals/tables?
That’s one of the beauties of it - it works equally well both ways.
@BrianJ , is there a way to solve this using DAX by manipulating the filters? From my understanding using the ISAFTER logic in the date table requires you to place a filter on the visual or page. In my circumstance I want to be able to look at Actuals say 24 months back from the previous month, we are in December 2021 so that would be from November 2021 back 24 months. This would exclude any future orders. This would be one measure. We would also want to look at the future forecast out 24 months from the current month (so from December 2021 and forward). This would exclude any historical forecast that might come in. This would be the second measure. All of this would need to be dynamic as well as the month changes this would update. I am looking for the flexibility to have the visual show the range of 24 months back and 24 months forward for each of these two measures in the same visual.
Please start a new topic as this posting is already solved. You will get better results if you start a new topic as members sometimes don’t check postings that is already solved.