PDiddy

2015-09-01, 06:18 PM

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.