So a little background:
I have a table with actual costs with dates. I have used a timeslicer within the pivottable so as to be able to toggle the pivot table with the period information that I want to view.
I will also have 12 forecast tables that will forecast the costs for the remainder of the months that are not actual.
For example:
Actual costs = JAN & FEB
Forecast costs = MAR to DEC
So in this case the year end forecast will be the actual costs until FEB + the forecasts for the remainder of the months.
Now here is the tricky part:
When a user of the report toggles the timeslicer for actual back to JAN I will need the year end forecast to be actual costs until JAN + the forecasts for the remainder of the months
Also note: I will have 12 seperate forecast tables as the forecast is revised every month
So what I think I need is a DAX whatif formula that will sum different forecast tables based on the period selected in the timeslicer. (On a form control you can do a cell link that will provide a value based on the position of the control - if this is possible with a timeslicer the value or month could be used as a condition within the whatif formula)
Any help with this problem would be certainly appreciated.
Thanks.
I have a table with actual costs with dates. I have used a timeslicer within the pivottable so as to be able to toggle the pivot table with the period information that I want to view.
I will also have 12 forecast tables that will forecast the costs for the remainder of the months that are not actual.
For example:
Actual costs = JAN & FEB
Forecast costs = MAR to DEC
So in this case the year end forecast will be the actual costs until FEB + the forecasts for the remainder of the months.
Now here is the tricky part:
When a user of the report toggles the timeslicer for actual back to JAN I will need the year end forecast to be actual costs until JAN + the forecasts for the remainder of the months
Also note: I will have 12 seperate forecast tables as the forecast is revised every month
So what I think I need is a DAX whatif formula that will sum different forecast tables based on the period selected in the timeslicer. (On a form control you can do a cell link that will provide a value based on the position of the control - if this is possible with a timeslicer the value or month could be used as a condition within the whatif formula)
Any help with this problem would be certainly appreciated.
Thanks.