Create a forecast without data model, just from what-if parameters

DAX DAX Calculations
1

Hi,

I am trying to build a dynamic break even analysis. The idea is that the user can choose a single selection for these parameters:

  • ‘Monday sales’, 'Tuesday sales’ … ’Sunday sales’ (one for each day of the week) between a given range of values.
  • ‘Monthly Fixed cost’ between a given range of values
  • ‘Monthly Product cost’ between a given range of percentages.

Once completed, then, what I think I have to do, is:

  • Create a daily forecast considering the values selected for each day of the week (i.e: June 2020 has 5 mondays & tuesdays, 4 wednesday…)
  • Change the granularity of the Fixed and Product cost to daily.
  • Summarize both type of cost to build a Total Cost measure.
  • Divide the Total Cost by the number of days, so I can create a Daily Cost
  • Create two measures for Cumulative Forecast and Cumulative Cost.
    Represent both Cumulative measures in a visual.

I am stocked in creating the Daily Forecast. I do not know how to build it from the choices made. I have the choices in measures but I do not know how to using them to get a physical or a calculated table with the daily forecast data. I have tried (UNION, ROWs, ADDCOLUMNS… but I always get a 0 value). I am trying to find a workaround through physical queries but I do not see how to build the relationships…

If anyone can provide me any direction on where to progress I would appreciate.

Thanks !

Juanjo

2

Your question is a little bit unclear. Could you provide some context? Sample data or PBIX file or an image would be helpful. If you don’t have PBIX file maybe you can draw it out the layout and share?

Thanks.

3

Thanks for your replay. I think the pbix file confuses even more, as I do not know what is the right approach. I have built an excel file which shows the forecast I would need to recreate in Power BI, and then its representation.

Hope make it clearer,

forecast
forecast1610×787 69 KB

Thanks

4

Hi @juanjo

As you are trying to figure out how to do it, it would be a good idea to watch some of the videos of the Budgeting and Forecasting course by @sam.mckay. Unfortunately my mobile is playing up and not letting me share the link of the videos. Please find it in the training section,hopefully they will be helpful.

Thanks and regards

Najah

5

Thanks @NajahS. Yes, of course, I am watching all of them, but they are based in a historical fact table (sales) that It’s not relevant in my case (in fact it does not exist) All the forecast is based in what-if parameters, and the way to set those parameters in a table is not working at all.

I am watching all of them to see if I find any workaround. If someone has an idea on where to investigate…

Thanks

6

Trying to get the picture clear, you are trying to get the forecast based on the selection that the user makes? I was wondering if you have thought of using a dropdown list for the selection criteria? I feel that if we use couple of slicers for the selection criteria, it would give you the results.
Please let us know if you need more help
Regards
Najah

7

Thanks @NajahS, that’s exactly what I did. I created the slicers and get the results in different measures. The problem was when I tried to generate the forecast in a calculated table, I do not know how to do it without starting from a fact table…

I am progressing by creating a measure ‘Daily sales’ using the switch function and the Cumulative by following:

8

I hope it gets sorted for you, if not please don’t hesitate to ask again. We do have some champs on the forum who might be able to come up with some wonderful solution.
Take care
Najah

9

Thanks @NajahS.

11

Thanks for posting your question @juanjo. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; 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.

Including all of the above will likely enable a quick solution to your question.

12

Hi @juanjo, we’ve noticed that no response has been received from you since the 12th of April. 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!