Calculating running total between two random dates

[RichardRayJH]

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]

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]

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

[TABLE="width: 399"]
<tbody>[TR]
[TD]Dates[/TD]
[TD="align: right"]Net[/TD]
[TD="align: right"]Period to Date[/TD]
[/TR]
[TR]
[TD]11/22/2012[/TD]
[TD="align: right"]$4,625[/TD]
[TD="align: right"]$99,739[/TD]
[/TR]
[TR]
[TD]11/23/2012[/TD]
[TD="align: right"]$21,339[/TD]
[TD="align: right"]$99,739[/TD]
[/TR]
[TR]
[TD]11/24/2012[/TD]
[TD="align: right"]$73,776[/TD]
[TD="align: right"]$99,739[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 383"]
<tbody>[TR]
[TD]Dates[/TD]
[TD="align: right"]Net[/TD]
[TD="align: right"]Period to Date[/TD]
[/TR]
[TR]
[TD]11/22/2012[/TD]
[TD="align: right"]$4,625[/TD]
[TD="align: right"]$4,625[/TD]
[/TR]
[TR]
[TD]11/23/2012[/TD]
[TD="align: right"]$21,339[/TD]
[TD="align: right"]$25,964[/TD]
[/TR]
[TR]
[TD]11/24/2012[/TD]
[TD="align: right"]$73,776[/TD]
[TD="align: right"]$99,739[/TD]
[/TR]
</tbody>[/TABLE]

 

[marco.russo]

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]

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:

[TABLE="width: 406"]
<tbody>[TR]
[TD]Dates[/TD]
[TD="align: right"]Net[/TD]
[TD="align: right"]MiguelsPTD[/TD]
[TD="align: right"]MarcosPTD[/TD]
[/TR]
[TR]
[TD]11/24/2012[/TD]
[TD="align: right"]$73,776[/TD]
[TD="align: right"]$212,871[/TD]
[TD="align: right"]$180,421,103[/TD]
[/TR]
[TR]
[TD]11/25/2012[/TD]
[TD="align: right"]$47,486[/TD]
[TD="align: right"]$212,871[/TD]
[TD="align: right"]$180,468,590[/TD]
[/TR]
[TR]
[TD]11/26/2012[/TD]
[TD="align: right"]$45,683[/TD]
[TD="align: right"]$212,871[/TD]
[TD="align: right"]$180,514,273[/TD]
[/TR]
[TR]
[TD]11/27/2012[/TD]
[TD="align: right"]$45,926[/TD]
[TD="align: right"]$212,871[/TD]
[TD="align: right"]$180,560,198[/TD]
[/TR]
</tbody>[/TABLE]


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]

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]

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:

[TABLE="width: 319"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Dates[/TD]
[TD="align: right"]Net[/TD]
[TD="align: right"]PTD[/TD]
[/TR]
[TR]
[TD]11/24/2012[/TD]
[TD="align: right"]$73,776[/TD]
[TD="align: right"]$73,776[/TD]
[/TR]
[TR]
[TD]11/25/2012[/TD]
[TD="align: right"]$47,486[/TD]
[TD="align: right"]$47,486[/TD]
[/TR]
[TR]
[TD]11/26/2012[/TD]
[TD="align: right"]$45,683[/TD]
[TD="align: right"]$45,683[/TD]
[/TR]
[TR]
[TD]11/27/2012[/TD]
[TD="align: right"]$45,926[/TD]
[TD="align: right"]$45,926[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Laurent C]

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]

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]

APL? Not that common.