Though out 2021, we will reforecast after every month. The Name of each forecast will be like this; “1+11” - It will have Jan 2021 with all zero’s, but the next 11 months will have forecasts. “2+10” = It will have Jan and Feb 2021 with all zero’s, but the next 10 months will have forecasts…and so and will end at “11+1” - first 11 months will have all zero’s with forecasts for Dec 2021. All of these multiple forecasts will be rolled up into on file.
I created a measure that totals the forecasts, and I have a measure that will display my actuals.
We want to select “1+11” in Feb and see the Actuals for Jan and the forecasts for the rest of the year from the “1+11”. When we click on “2+10” in March, we want to see actuals for Jan and Feb and the forecasts for the rest of the year from the “2+10”.
Please review the image I posted. I need a measure that says: If the forecasted total from the month/year equals zero, then give me actuals, if the forecasted total for the month/year is greater than 1, give me forecasts.
When we are in October, we still want to click on “1+11” and just see actuals for Jan and our forecast for the rest of the year from the “1+11” portion of the forecast. So, the measure can not ask, if actuals exist, give me actuals, else forecasts. I hope this makes sense. Thanks for the help.
Hi @Usates. Can you please provide a sample dataset, work-in-progress PBIX file, and an Excel mock-up of the desired outcome for a couple of slicer selections to help the forum members further investigate your issue?
Greg
Could you please post the link to the video that shows us how to strip out sensitive information in our bi file? I remember seeing it once but can’t find it now.
When the “Name” is “8+4”, I need the [Actual]'s for Jan - Aug, and the [Budget] numbers for Sept-Dec. When the “Name” is “9+3”, I need the [Actual] for Jan-Sept, and the [Budget] numbers for Oct-Dec…and so on.
“8+4” means that for the first 8 months, the budget = zero for each month, and for the last 4 months, the budget > 0. Same with the rest. By the end of 2021, we will have 1+11, 2+10, 3+9, 4+8, 5+7, 6+6, 7+5, 8+4, 9+3, 10+2, 11+1. We want to switch between anyone of these and see how our budgets have changed over time. Once again, we will re-forecast every month. So at the first of July, we will append our 6+6 budget, which will have Jan-June blank(because we will now have actuals for those months), and re-forecasts for July - Dec.
There’s a major issue with the file you’ve provided because your model does not include a Date dimension table (the “Posting Period” does not meet the requirements for a valid date table). For your learning I strongly recommend you review all course material on that AND that you rework the Model yourself. You can find M code for several Date table types in the M code showcase category like the Extended Date table here.
I have a full date table but did not include it into this sample as I assumed Accounting Period Internal ID in the budget, transaction lines, and posting period tables were enough to satisfy the result I am trying to achieve. Is my solution impossible without a full date table?
Here’s my perspective from a Modelling point of view.
Fact tables should contain a single key to one or more Dim tables. There are exceptions of course for example when you have a fact table that contains multiple Date fields. However, Fact tables should never contain attributes that belong to a Dim table.
Looking at your Model these fields, except for a single key, can be considered attributes: Budget & Forecast columns: Name, Period and Period2 Posting Period is completely Date table related
Having attributes in one or more Fact tables comes at a cost, often fact tables are far larger than dim tables, needlessly increasing model size. While a single well designed Dim table lets you leverage all other attributes from that table to perform your analysis effectively.
So to answer your question “is it impossible without a full date tabel?”
No you can probably get it to work (if you don’t require any DAX time intelligence functions), but filter context can become an issue in your model, leading to questionable results. Your DAX will become more complicated and performance will suffer.
My advise remains build an effective and solid Model before you start writing any DAX.
Hi @Usates, did the response provided by the users and experts helped you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Hi @Usates, we’ve noticed that no response has been received from you since the 2nd of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. 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 checkbox. Thanks!
I love this forum and get great advise from it. Unfortunately, this time, I did not receive an answer to my issue. I was able to find a solution that worked with my model. My model is the model I have to work with. I am not the administrator, where I can make wholesale changes to my dataset. It’s not perfect but it does work. I appreciate everyone from this forum and will continue to post issues I have as they arise. You guys are awesome!
