Rolling 90 Day Total

[Capt.Ragnar]

I've tried to apply examples that I found here and elsewhere to no avail. Below is the DAX formula I've attempted without success...

90DaysAmount:=CALCULATE(SUMX(Table1,[Amount]),FILTER(ALL(Table1),Table1[Time ID]>=MAX(Table1[Time ID])-3&&Table1[Time ID]<=MAX(Table1[Time ID])))

What you need to know about the time component is that data is only reported monthly. So each number value in the Time ID table is reflective of a month and a year.

Ex.

[TABLE="width: 200"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jan[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Feb[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mach[/TD]
[TD]2013[/TD]
[/TR]
</tbody>[/TABLE]


I'm trying to calculate the 90 day volume for each location in my pivot. The formula above results in the same total for every location.

 

[scottsen]

The weird thing for me here is that I would expect to see calendar table, but it looks like everything is coming from 1 table?

 

[XLBob]

Try this, it should give u what you want IF(HASONEVALUE(Table1[ID]),CALCULATE([TotalAmount],FILTER(ALL(Table1),(MAX(Table1[ID])-Table1[ID])=3)))
Note [TotalAmount]=sum(Table1[Amount])

 

[Capt.Ragnar]

The weird thing for me here is that I would expect to see calendar table, but it looks like everything is coming from 1 table?

True. I tried to normalize the formula for easier display, but in the process only reference one of the two tables. Here is the actual formula.

90DaysAmount:=CALCULATE(SUM([Amount]),ALL(MergeFinDataMetricDenom),FILTER(ALL('Fin Cube_TimeKey'),'Fin Cube_TimeKey'[Time]>=MAX('Fin Cube_TimeKey'[Time])-90&&'Fin Cube_TimeKey'[Time]<=MAX('Fin Cube_TimeKey'[Time])))

 

[scottsen]

You include this: ALL(MergeFinDataMetricDenom)

Are fields from that on your pivot? Cuz that would wipe the fields context...

 

[Capt.Ragnar]

You include this: ALL(MergeFinDataMetricDenom)

Are fields from that on your pivot? Cuz that would wipe the fields context...

Yes. The financial data is in 'MergeFinDataMetricDenom'. The Time ID is in 'MergeFinDataMetricDenom'. The dates and the Time ID are in 'Fin Cube_TimeKey'. Relationships are created and working properly.

 

[scottsen]

I'm unclear on the purpose of the ALL(MergeFinDataMetricDenom), but if that are fields on rows/columns/filters/slicers from that table, the all is going to clear that... and thus, give you the same value in every cell (which is what you are reporting).

Any chance removing that ALL magically fixes your problems?

If not, maybe share your workbook, or at least a paste of more of the (input) tables and the resulting pivot table.

 

[Capt.Ragnar]

I thought the same as yourself. If I dump the All I'm still getting rolled up totals for each entry for Amount.

Its hard to explain so here is an example of how the Amount column is structured. I hope this makes sense? BTW I'm using a slicer to select across the various measures and need to see the 90 total of the amount.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Scenario ID
[/TD]
[TD]Location ID
[/TD]
[TD]Time ID
[/TD]
[TD]Measure
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Net Rev
[/TD]
[TD]200000
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Adj. Net Rev
[/TD]
[TD]180000
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Total Units
[/TD]
[TD]170
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Total Customers
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]Net Rev
[/TD]
[TD]210000
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Capt.Ragnar]

*Ignore the previous post I omited the Time Table

I thought the same as yourself. If I dump the All I'm still getting rolled up totals for each entry for Amount.

Its hard to explain so here is an example of how the Amount column is structured. I hope this makes sense? BTW I'm using a slicer to select across the various measures and need to see the 90 total of the amount.


Fin Data
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Scenario ID
[/TD]
[TD]Location ID
[/TD]
[TD]Time ID
[/TD]
[TD]Measure
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Net Rev
[/TD]
[TD]200000
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Adj. Net Rev
[/TD]
[TD]180000
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Total Units
[/TD]
[TD]170
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Total Customers
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]Net Rev
[/TD]
[TD]210000
[/TD]
[/TR]
</tbody>[/TABLE]

Time Table

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Time
[/TD]
[TD]Month
[/TD]
[TD]Year
[/TD]
[TD]SlicerValue
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1/1/2013
[/TD]
[TD]Jan
[/TD]
[TD]2013
[/TD]
[TD]01- Jan
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2/1/2013
[/TD]
[TD]Feb
[/TD]
[TD]2013
[/TD]
[TD]02- Feb
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3/1/2013
[/TD]
[TD]Mar
[/TD]
[TD]2013
[/TD]
[TD]03- Mar
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4/1/2013
[/TD]
[TD]Apr
[/TD]
[TD]2013
[/TD]
[TD]04- Apr
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]5/1/2013
[/TD]
[TD]May
[/TD]
[TD]2013
[/TD]
[TD]05- May
[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

Now I am probably even more confused

First things first, I would see this table and immediately start writing "base measures".

[Total Units] := CALCULATE(SUM(Table1[Amount], Table1[Measure] = "Total Units"))
[Total Net] := CALCULATE(SUM(Table1[Amount], Table1[Measure] = "Net Rev"))

etc.

There is never a reason to look at the Amount column outside these "filtered" calculates, because comparing Total Units to Net Rev is crazy talk.

Then I expect your 90 day amount to look something like:

 [90DayUnits] := CALCULATE([Total Units], 
                             FILTER(ALL(Calendar), 
                                       Calendar[Time Id] > MAX(Calendar[Time Id] - 3 && 
                                       Calendar[Time Id] <= MAX(Calendar[Time Id])
                                       )
                         )