Results 1 to 8 of 8

Thread: Dax formula to sum actual and forecast columns

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Dax formula to sum actual and forecast columns

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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:

    Code:
    IF(    MONTH(LASTDATE(Calendar[DateKey]))<4,
        BLANK(),
        CALCULATE(
            [Monthly Transactions],
            DATESBETWEEN(
                Calendar[DateKey],
                DATEADD(
                    STARTOFYEAR(Calendar[DateKey]),
                    3,
                    MONTH
                    ),
                DATEADD(
                    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, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Wait, one clarification here. [Monthly Transactions] is not a table. It's a measure with the following formula:
    Code:
    Monthly Transactions:=SUM([Amount])
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    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.

    Attached Files Attached Files

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •