locked
Running total within a context RRS feed

  • Question

  • Loading a pivot model from an excel workbook we encountered the following problem.

    The excel columns (simplified):

    Date Team Hours

    The sheet is loaded in a pivot table TeamHours and we want to calculate the running total of the Hours field.

    So we do a calculation:

    TeamHours[HoursRunningTotal]=

    SUMX(FILTER(TeamHours; TeamHours[Date]<=EARLIER(TeamHours[Date]]);

    TeamHours[Hours])

    This works fine as long as the datasheet contains only one Team.

    If the sheet contains two or more teams the values of all teams are summed. On a certain date for a specific team the values of all the previous dates are summarized disregarding the team context.

    What we want to know is a runningtotal on hours in the context of the team.

    To solve this problem  now we calculate the running total at the moment the sheet is loaded in the pivot, but is this the best way to do it? Can it be done using DAX?

    We tried to add the Team-field  to the Filter but that didn’t work.

    • Changed type Michael Amadi Wednesday, December 3, 2014 9:56 AM This is a specific question as opposed to a general discussion.
    Tuesday, December 2, 2014 4:35 PM

Answers

  • Try this:

    =SUMX(
            FILTER(
                  TeamHours, 
                  TeamHours[Date] <= EARLIER(TeamHours[Date]) && 
                  TeamHours[Team] = EARLIER(TeamHours[Team])
            ), 
            TeamHours[Hours]
    )

    • Proposed as answer by Michael Amadi Wednesday, December 3, 2014 9:56 AM
    • Marked as answer by Wim Wensel Wednesday, December 3, 2014 9:57 AM
    Tuesday, December 2, 2014 7:47 PM
    Answerer

All replies

  • Try this:

    =SUMX(
            FILTER(
                  TeamHours, 
                  TeamHours[Date] <= EARLIER(TeamHours[Date]) && 
                  TeamHours[Team] = EARLIER(TeamHours[Team])
            ), 
            TeamHours[Hours]
    )

    • Proposed as answer by Michael Amadi Wednesday, December 3, 2014 9:56 AM
    • Marked as answer by Wim Wensel Wednesday, December 3, 2014 9:57 AM
    Tuesday, December 2, 2014 7:47 PM
    Answerer
  • This works! Many thanks.

    Regards

    Wim

    Wednesday, December 3, 2014 9:54 AM