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