PDA

View Full Version : Dax formula to sum actual and forecast columns

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.

Ken Puls
2015-09-02, 07:13 PM
Hmm... this one is a bit complicated, but can certainly done. My suggestion would be to break it down a bit, allowing you to test out your individual formulas as you go.

When I last built a schedule like this, I built 12 individual columns for each month, as well as a total column to add them all up. This allowed me to get quite granular for each column. Now the purpose of my statement was to allow the user to select the months for actuals, and only have a 0 in any column after that, but you could easily flip the 0 logic to put in the logic you need.

Here's a DAX formula to look at which I used for my April column:

IF( MONTH(LASTDATE(Calendar[DateKey]))<4,
BLANK(),
CALCULATE(
[Monthly Transactions],
DATESBETWEEN(
Calendar[DateKey],
STARTOFYEAR(Calendar[DateKey]),
3,
MONTH
),
ENDOFMONTH(STARTOFYEAR(Calendar[DateKey])),
3,
MONTH
)
)
)
)

So remember here, the goal was to show 0 if the user only selected a date up to Mar 31, but show the Apr values from the [Monthly Transactions] table if the date selected in the Timeline included a single data of April or later.

I first tested to see if the last date selected had a month less than 4. If it did, I used BLANK() to return a blank to the Pivot, which was formatted to show blank values as 0. (I could have used 0, but that would show every account, so this way I only saw accounts where transactions existed in Jan-Mar already.) This is the area where you'd put in the correct call to the forecast table you want.

The big Calculate clause is overriding the dates in the timeline, forcing them to only return dates from Apr 1 - Apr 30. (Remember that adding 3 months to the StartOfYear will return Apr 1, not Mar 1. You'll probably need to build a similar Calculate to this against your forecast table, depending on whether each forecast table has 12 months or not.

Hope this helps!

Ken Puls
2015-09-02, 07:18 PM
Wait, one clarification here. [Monthly Transactions] is not a table. It's a measure with the following formula:

Monthly Transactions:=SUM([Amount])

:)

PDiddy
2015-09-02, 08:57 PM
Thanks for yourresponse Ken... but there must be an easier way...

What I was hoping to do is simply use the period number as a trigger for the ifstatement. I thought I had it but the average month calculation only updates inthe pivot table as it is connected to the timeslicer, however the value doesnot change in the powerpivot model. Is there a way to make this value dynamicso that I can use it as a trigger?

See attached file.

Thanks.

Ken Puls
2015-09-02, 09:21 PM
Hey there,

Okay, so looking at your data, I think the issue is that your data isn't really in a good format to get what you're looking for here. Are you able to use Power Query for this as well, or is that a non-starter?

The reason I'm asking is that what you really need to drive this solution is an Actuals and a Forecast table with dates. By separating things into multiple forecast tables, you're going to be going through a lot of pain to pull them back together, especially since the table names are inconsistent too. If you were able to use Power Query to append those tables together and add proper dates to them, I think this would be much easier...

PDiddy
2015-09-03, 12:41 AM
Thanks Ken... So yes I can use powerquery to merge and I have simulated this merger in the revised attached.

I have added date information to the forecast table so as to be able to timeslice the forecast on the pivot table as well.

I have also included the year end forecast column which adds the ytd actual and the forecast for the month which will be the forecast for the remainder of the year.

Now to clean this up... I would like to have only one timeslicer that the user needs to toggle... Is this possible in excel 2013? When you have multiple data sets such as budget, actual and forecast and even prior year actual being reported on one pivot table it can become just to much with four seperate timeslicers.

Any help would be appreciated.

Thanks.

Ken Puls
2015-09-03, 01:13 AM
Hey Mike,

I've modified your workbook in the following ways:
-Named your tables so I could have an idea of what fit where. (Hoping that you aren't really using Table1, etc... for table names in your real models)
-Moved the measures into different tables to get rid of the "Relationships are needed" errors.
-Added a calendar table (using Power Query)

Do yourself a favour today... never build another model without a calendar table. It's just bad news. You can see how I pulled it together, as I've left the Power Query script in there for you. :)

PDiddy
2015-09-03, 04:53 PM
Thanks Ken.... This is exactly what I was looking for. Its nice to see a different take on the design of the model structure as most of my models have been designed as a "pyramid" structure.