Show actuals when forecast totals equal 0

[Usates]

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.

[Greg]

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

[Usates]

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.

[Greg]

Here you go.
Greg

How To Mask Sensitive Data In Power BI

[Usates]

Sample.pbix (200.9 KB)

Here is my sample pbix file.

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.

[Melissa]

Hi @Usates,

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.

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! . let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonth as number, optional Holidays as list, optional WDStartNum as number ) as table => let FYStartMonth = if List.Contains( {1..12}, FYStartMonth ) th…

.
Here are some links to related content. You can use the Search option in the right upper corner of your screen to find more but this should get you started.

Why you must have a date table?

Creating The Ultimate Date Table In Power BI - How To Do This Fast (Updated Code)

Do I really need a full year in my Date table?

Hi @michellepace, In most cases when you have a date field in your model, you will want to do some time intelligence calculations and for that type of analysis this is indeed a requirement. Below an excerpt of this Microsoft article To work with Data Analysis Expressions (DAX) time intelligence functions, there’s a prerequisite model requirement: You must have at least one date table in your model. A date table is a table that meets the following requirements: It must have a column of dat…

Time Intelligence series by BrianJ and Melissa

Hi everyone, We’ve brought the Time Intelligence series we did for Enterprise DNA TV together in this post so it’ll be easier to find and navigate through completely. @BrianJ and I had great fun doing these. If you have any questions or concerns about the content covered in these video’s please create a new topic, refer to the video and @mention BrianJ or Melissa. But we’d also welcome your feedback within this thread of course. – THANK YOU – How to create a Holiday table M functi…

Reach out if you need any assistance.
I hope this is helpful.

[Usates]

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?

[Melissa]

Hi @Usates,

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.

[EnterpriseDNA]

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!

[EnterpriseDNA]

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!

[Usates]

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!