# Calculating running total between two random dates



## RichardRayJH (Feb 25, 2013)

Given 

1) a Calendar table with continuous dates in a column [DateDate]
2) an interface that allows the user to select a [StartDate] and an [EndDate]
3) the [StartDate] and [EndDate] might not be in the same year
4) a measure of some sort, say [Net Revenue], that calculates just fine for each date

I want a DAX formula that gives me a running total of [Net Revenue] starting on [StartDate] through [EndDate].

Date             Net Amount       Running Total
------------  ------------      ---------------
12/29/2012    5000                 5000
12/30/2012    6000                11000 
12/31/2012    2500                13500
01/01/2013    1000                14500
01/02/2013    2000                16500

My usual ploy of just finding someone who's already done it and posted it in a blog or a book hasn't worked and I'm stumped with my own newbie skills, HELP.


----------



## miguel.escobar (Feb 25, 2013)

Let's say that the user selects the START DATE and END DATE from the same slicer, then he wouldn't have any problem getting the right calculation of the desired NET REV...
In terms of using parameters then you'd have to approach this scenario with CUBEFORMULAS
If you happen to create the cubeformula you could create a measure like the following to use it as a CUBEVALUE field or cell
=CALCULATE ( [Net Rev], FILTER(ALL(Dates Table), START_DATE >= [datesfield] && [datesfield] <= END_DATE) )

Hope this helps


----------



## RichardRayJH (Feb 25, 2013)

I don't think that's quite it.  Using that formula in my environment with [Current Start] = 11/22/2012 and [Current End] = 11/24/2012 looks like:

[Period to Date] = CALCULATE ( [Net Amount], FILTER(ALL(Calendar), [Current Start] <= Calendar[DateDate]  && Calendar[DateDate] <= [Current End]) ) * [Is In Date Range]

gives me


DatesNetPeriod to Date11/22/2012$4,625$99,73911/23/2012$21,339$99,73911/24/2012$73,776$99,739

<tbody>

</tbody>

The Season to Date value is the sum of all the rows, where I'm looking for the sum of the rows down to and including each row. i.e. -


DatesNetPeriod to Date11/22/2012$4,625$4,62511/23/2012$21,339$25,96411/24/2012$73,776$99,739

<tbody>

</tbody>


----------



## marco.russo (Feb 25, 2013)

The ALLSELECTED function can help you in this case.
Use it instead of ALL.

```
[Period to Date] :=
CALCULATE ( 
    [Net Amount],
    FILTER( 
        ALLSELECTED( Calendar ), 
        Calendar[DateDate] <= MAX( Calendar[DateDate] ) 
    ) 
)
```


----------



## RichardRayJH (Feb 25, 2013)

Thanks, but that's not getting it either.

I suspect that has to do with the fact that all the dates in Calendar are selected all the time in my model. I use [Is In Date Range] to pick out the values I want to use. [Is In Date Range] is an array of size (MaxDate - MinDate +1) containing a BLANK() where the date is not to be included and a 1 where it is. The interface I wanted is built on that. I need to allow the user to pick out a Holiday or Event the actual date for which moves from year to year - think Easter (although in the U.S. there are several such observances). Each year's values are then based on an offset from the base year's event date. In our business (operating a large ski resort) we care much more about the day than the date.

in any case, Miguel's formula gives me the total for the dates included, and yours gives me a slice of the running total for all dates from min to max:


*Dates**Net**MiguelsPTD**MarcosPTD*11/24/2012$73,776$212,871$180,421,10311/25/2012$47,486$212,871$180,468,59011/26/2012$45,683$212,871$180,514,27311/27/2012$45,926$212,871$180,560,198

<tbody>

</tbody>

The differences between your totals are the right increments (e.g. - 180,468,590 - 180,421,103 is indeed 47,486), so if I could find a way to 'reset' the total at the beginning of the included dates I'd have the right numbers!

If you've got the time to look at this I've got a version of the workbook with our proprietary numbers obfuscated, but it's a somewhat byzantine 10 MB (compressed) file and it would take some time to figure out just *what *the heck I'm doing, let alone how to *help *me...

Of course, if you ski or snowboard and ever get to the U.S. in the winter, or parapente, ride mountain bikes or climb mountains and you visited in the summer, maybe we can work something out. 

www.jacksonhole.com


----------



## miguel.escobar (Feb 25, 2013)

So, it's like 2am in Italy and a flight from Panama is cheaper than one from Italy...so I'm going to steal this one from Marco  [Period to Date] :=
CALCULATE ( 
    [Net Amount],
    FILTER( 
        ALLSELECTED( Calendar ), 
        Calendar[DateDate] <= MAX( Calendar[DateDate] ),                                                                                                                                                                                                                                              FILTER( 
        ALLSELECTED( Calendar ), 
        Calendar[DateDate] >= MIN( Calendar[DateDate] )
    ) )
)


----------



## RichardRayJH (Feb 25, 2013)

Don't book that flight quite yet .

Pasting your formula threw an error that FILTER had too many args. It looked like there was a missing paren in the first FILTER, so I rewrote it as:

PTD := CALCULATE 
( [Net Amount],
FILTER( ALLSELECTED( Calendar ), Calendar[DateDate] <= MAX( Calendar[DateDate] )*)*, 
FILTER( ALLSELECTED( Calendar ), Calendar[DateDate] >= MIN( Calendar[DateDate] ))
) *[Is In Date Range]

The new paren is in *Bold* *Red *above.

That gives me:


*Dates**Net**PTD*11/24/2012$73,776$73,77611/25/2012$47,486$47,48611/26/2012$45,683$45,68311/27/2012$45,926$45,926

<colgroup><col><col><col></colgroup><tbody>

</tbody>
Are you a skier or snowboarder? Fly parapente, climb, fish? Jackson Hole is a pretty nice place. Marco snoozes, Marco loses.


----------



## Laurent C (Feb 25, 2013)

If I understand this correctly, you want to calculate a running value for all dates that are between your selected Start and End date.

Here is my totally untested suggestion:


```
[Period To Date] :=
CALCULATE( [Net Revenue],
 FILTER( 
  ALL(Calendar),
  [Current Start] <= Calendar[DateDate] 
   && Calendar[DateDate] <= [Current End]
   && Calendar[DateDate] <= MAX(Calendar[DateDate])
 )
)
```

ALLSELECTED could also be used in case you want to directly add filters on your Calendar table and want the calculation to respond accordingly.

As a side-note, and unless I missed something in PowerPivot, [Is In Date Range] is a measure, not an array. In other words, there is no array product in PowerPivot.


----------



## RichardRayJH (Feb 25, 2013)

re arrays in PP: you are right, of course, there are no arrays in PP. [Is In Date Range] is actually a measure that returns '1' if the current Calendar[DateDate] is in the range being considered, and BLANK() otherwise.

OTOH, coming from environments that do support arrays, and viewing the output in my workbook when i use it as indicated above, I find it a useful construct, and I think of it on those terms.

Actually, as an old APL programmer (is there any other kind?), I view spreadsheets in general as nice interfaces for array processing .

I'll be more concise in the future....


----------



## Laurent C (Feb 25, 2013)

APL?  Not that common.


----------



## RichardRayJH (Feb 25, 2013)

Given 

1) a Calendar table with continuous dates in a column [DateDate]
2) an interface that allows the user to select a [StartDate] and an [EndDate]
3) the [StartDate] and [EndDate] might not be in the same year
4) a measure of some sort, say [Net Revenue], that calculates just fine for each date

I want a DAX formula that gives me a running total of [Net Revenue] starting on [StartDate] through [EndDate].

Date             Net Amount       Running Total
------------  ------------      ---------------
12/29/2012    5000                 5000
12/30/2012    6000                11000 
12/31/2012    2500                13500
01/01/2013    1000                14500
01/02/2013    2000                16500

My usual ploy of just finding someone who's already done it and posted it in a blog or a book hasn't worked and I'm stumped with my own newbie skills, HELP.


----------



## miguel.escobar (Feb 25, 2013)

what if...instead of having the dates that you want to analyze as a measure, you create a dax measure using CALCULATE TABLE or ADD COLUMNS to define the dates that you're trying to analyze? I've been playing around with your file but for some reason I've not reached the solution (without using the running total feature of pivot tables) because when we use MIN, MAX, FIRSTDATE, LASTDATE it's actually querying ALL the set of rows meaning the first date that you have there but not the first one that you want to start your calculation. And I haven't had a chance to really look at what's going on in those tables...Even using Laurent's code is not doing it.


----------

