The As-Of Table

[hic]

Last week I wrote about how the Above() function can be used for calculating rolling averages and other accumulations. There is however also an alternative method for doing the same thing:

The As-Of table.

When you use the Above() function, you fetch a number from other rows in a chart or Aggr() table. The As-Of table is slightly different in this respect: It is not a transient table created by an object or an expression – instead it is a real table in the data model.

The idea is to create a secondary month field – the AsOfMonth - that links to multiple real months.

In the example above, you can see that ‘2015 Oct’ links to several preceding months, and each Month in turn links to several rows in a fact table. This means that a specific transaction will be linked to several AsOfMonths.

In the data model, the As-Of table should appear as a separate calendar table that links to the existing primary calendar table:

One way to create this table is the following:

First, make sure that you in your master calendar have a field “Month” that is defined as the first date of the month, e.g.

Date(MonthStart(Date),'YYYY MMM') as Month,

Then add the following lines at the end of the script:

// ======== Create a list of distinct Months ========
tmpAsOfCalendar:
Load distinct Month
  Resident [Master Calendar] ;

// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month as AsOfMonth
  Resident tmpAsOfCalendar ;

// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month,
  AsOfMonth,
  Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
  Year(AsOfMonth)-Year(Month) as YearDiff
  Resident tmpAsOfCalendar
      Where AsOfMonth >= Month;

Drop Table tmpAsOfCalendar;

Once this table has been created, you can use the AsOfMonth as dimension in charts where you want rolling averages and accumulations.

If you as measure use

Sum({$<YearDiff={0}>} Sales)

you will get a yearly accumulation – year-to-date up until the day of the script run.

If you instead use

Sum({$<MonthDiff={"<6"}>} Sales) / Count(distinct {$<MonthDiff={"<6"}>} Month)

you will get a 6-month rolling average:

And finally, if you use

Sum({$<MonthDiff={0}>} Sales)

You will get the real, non-accumulated numbers.

I have made the Set Analysis expressions based on two fields: YearDiff and MonthDiff. However, for clarity it could be a good idea to add flags in the As-Of table, so that the Set Analysis expressions become even simpler, e.g.

If(MonthDiff=0,1,0) as IsSameMonth,
If(YearDiff=0,1,0) as IsSameYear,
If(MonthDiff<6,1,0) as IsRolling6,

Summary: The As-Of table is a good way to calculate rolling averages and accumulations.

HIC

Further reading related to this topic:

The Above Function

Accumulations

Calculating rolling n-period totals, averages or other aggregations

[Anonymous]

if you tell about your specific use case, experts here can guide you more properly.

you can use button with action selection>select in field and search string to fetch all dates less than or equal to max date

As mentioned by Henric, you should see the no of records in the as of table. You can calculate the records that as-of table will have with the sum of arithmetic series

no of records = total no of dates or days * (1+no of last day ) / 2

So if you have four years of data, your as of table will have

(4*365)*(1+4*365)/2 =  1,066,530 records

Shoaib

[Anonymous]

Struggling with rolling months data sometimes before. Now I find the better solution. Thanks, hic‌.

And also thanks for explain the formula for the records! @Shoaib Ahmed.

Zhihong

[Anonymous]

Hats off to Henric‌ hic‌ and Gysbert gwassenaar‌ for this super-duper As-Of table

[Olip]

What about quarter? The ask is since I am looking at implementing it for rolling quarters.

[c_pannese]

Hi .

Is it possible to obtaine the same result but for successive months?

For example for january 2017 I need to use the value of march 2017.

Thanks a lot.

[kinahan7]

Can this method be used to compare consecutive months?  Compare December 2017 vs January 2018 or Jan vs Feb?

Thanks

[Anders_Eriksson]

Sure it could but why?
Both faster and easier with set analysis.

[hic]

The problem of comparing two months is not solved by the As-of table, but it can be solved in a number of other ways. I would use YearMonth as dimension, e.g.

Date(MonthStart(Date),'YYYY-MMM') as YearMonth

and then calculate a change from previous month using

Sum(Sales) - Above(Sum(Sales))

This way you will get the difference between Feb and Jan to show up as the Feb number.

HIC

[kinahan7]

I'm looking to make the calculation work with multiple dimensions in a custom pivot or ClimberCustomReport in Sense where dimensions can be added and removed.  I think aggr and above can get the desired result, but I was hoping the expression could be fluid and work with multiple dimensions.  Below is what I'm trying to do using PowerBi and the PREVIOUSMONTH function.

This type of view-

Then bring in State, County, City or Zip

[Gysbert_Wassenaar]

Current month versus previous month can be done with an As-Of table. For the current month the value of MonthDiff is 0, for the previous month it will be 1.

Current month: Sum({$<MonthDiff={0}>} Sales)

Previous month:Sum({$<MonthDiff={1}>} Sales)

You could also add flag fields for this in the As-Of table:

If(MonthDiff=0,1,0) as IsSameMonth

If(MonthDiff=1,1,0) as IsPreviousMonth

The set analysis expressions will then look like

Current month: Sum({$<IsSameMonth={1}>} Sales)

Previous month:Sum({$<IsPreviousMonth={1}>} Sales)