Use cases for Time Dimensions

The following use cases are examples from AtScale's Sales Insights demo data, in which time dimensions are used for time-based sales analysis. The calculated measures shown in each MDX example use the following time intelligence MDX functions to navigate through time dimensions: LEAD, LAG, PREVMEMBER, NEXTMEMBER, CURRENTMEMBER, PERIODSTODATE, and PARALLELPERIOD.

Period-over-period growth for sales amounts
Sales growth year-over-year
Sales year-to-date
Sales year-to-date by day, compared to previous year
Daily sales compared to 30-period moving average and standard deviation
30-period moving average compared to the previous year's
30-period moving average percent change compared to the previous year's

Period-over-period growth for sales amounts

In this use case, the calculated measures below can be used to compare sales growth of the current period to the previous period. These calculated measures use CURRENTMEMBER, LAG, and PREVMEMBER to calculate the sales amount for the previous period and the sales amount growth over the previous period.

Case 1

Calculated measure for sales amount for the previous period: [Measures].[Sales Amount-Order Retail445-Prev]

([Order Date Dimension].[Order Retail445].CurrentMember.PrevMember, [Measures].[salesamount1])

Case 2

Calculated measure for sales amount growth over the previous period: [Measures].[Sales Amount-Order Retail445-PrevPeriodGrowth]

([Order Date Dimension].[Order Retail445].CurrentMember, [Measures].[salesamount1])
-
[Measures].[Sales Amount-Order Retail445-PrevYear]

Workbooks

Figure 1. Tableau workbook use cases 1 and 2

/public/images/prev_prevperiodgrowth_tableau.png

Figure 2. Excel workbook for use cases 1 and 2

/public/images/prevperiod_prevperiodgrowth_excel.png

Sales growth year-over-year

In this use case, the calculated measures below can be used to find the internet sales growth over one year (between now and the same time last year). The MDX expressions below use CURRENTMEMBER and PARALLELPERIOD.

Case 3

Calculated measure for internet sales from last year: [Measures].[Sales Amount-Order Retail445-PrevYear]

(ParallelPeriod(
   [Order Date Dimension].[Order Retail445].[Order Reporting_Year],
   1,
   [Order Date Dimension].[Order Retail445].CurrentMember),
   [Measures].[salesamount1]
   )

Case 4

Calculated measure for growth of internet sales between last year and the current year: [Measures].[Sales Amount-Order Retail445-PrevYearGrowth]

([Order Date Dimension].[Order Retail445].CurrentMember, [Measures].[salesamount1])
-
[Measures].[Sales Amount-Order Retail445-PrevYear]

Workbooks

Figure 3. Tableau workbook for use cases 3 and 4

/public/images/prevyear_prevyeargrowth_pct_tableau.png

Figure 4. Excel workbook for use cases 3 and 4

/public/images/prevyear_prevyeargrowth_pct_excel.png

Sales year-to-date

In this use case, the calculated measure below can be used to find the internet sales for the year, ending with the current date.

Case 5

Calculated measure for the aggregate of sales for the year: [Measures].[Sales Amount-Order Retail445-YTD]

MDX expression:

Aggregate(
  PeriodsToDate(
      [Order Date Dimension].[Order Retail445].[Order Reporting_Year],
      [Order Date Dimension].[Order Retail445].CurrentMember
      ),
      [Measures].[salesamount1]
  )

Sales year-to-date by day, compared to previous year

In this use case, the calculated measure below can be used to compare the daily sales amount for the year-to-date, and the year-to-date sales of the year previous.

Case 6

Calculated measure for all internet sales of the year previous to the current year: [Measures].[Sales Amount-Order Retail445-YTDPrevYear]

MDX expression:

Aggregate(
  PeriodsToDate(
      [Order Date Dimension].[Order Retail445].[Order Reporting_Year],
      ParallelPeriod(
          [Order Date Dimension].[Order Retail445].[Order Reporting_Year],
          1,
          [Order Date Dimension].[Order Retail445].CurrentMember
          )
      ),
  [Measures].[salesamount1]
  )

Workbooks

Figure 5. Tableau workbook for use cases 5 and 6

/public/images/ytd_ytdprevyear_tableau.png

Figure 6. Excel workbook for use cases 5 and 6

/public/images/ytd_ytdprevyear_excel.png

Daily sales compared to 30-period moving average and standard deviation

In this use case, the calculated measures can be used to compare each day's sales for the past 30 days, as well as each day's moving average of the previous 30 days. We can also find the upper and lower bounds of the daily moving average's standard deviation.

Case 7

Calculated measure for the 30-period moving average: [Measures].[Sales Amount-Order Retail445-30PrdMvAvg] (Not that the Range operator ":" is inclusive at both ends of the range, so use a lag value of 29 to get a 30 period range.)

Avg(
  [Order Date Dimension].[Order Retail445].CurrentMember.Lag(29)
  :
  [Order Date Dimension].[Order Retail445].CurrentMember,
  [Measures].[salesamount1]
  )

Case 8

Calculated measure for the 30-period moving average's standard deviation: [Measures].[Sales Amount-Order Retail445-30PrdMvStdev]

Stdev(
  [Order Date Dimension].[Order Retail445].CurrentMember.Lag(29)
  :
  [Order Date Dimension].[Order Retail445].CurrentMember,
  [Measures].[salesamount1]
  )

Case 9

Calculated measure for the standard deviation's upper bound: [Measures].[Sales Amount-Order Retail445-30PrdMvUpperBand]

[Measures].[Sales Amount-Order Retail445-30PrdMvAvg]
+
[Measures].[Sales Amount-Order Retail445-30PrdMvStdev]

Case 10

Calculated measure for the standard deviation's lower bound: [Measures].[Sales Amount-Order Retail445-30PrdMvLowerBand]

[Measures].[Sales Amount-Order Retail445-30PrdMvAvg]
-
[Measures].[Sales Amount-Order Retail445-30PrdMvStdev]

Workbooks

Figure 7. Tableau workbook for use cases 7, 8, 9 and 10

/public/images/30prdmvavg_lowerupper_tableau.png

Figure 8. Excel visual representation for use cases 7, 8, 9 and 10

/public/images/30prdmvavg_lowerupper_excel.png

30-period moving average compared to the previous year's

This use case compares the moving average of sales for last 30 days, compared to the same period in the previous year.

Case 11

Calculated measure for the 30-period moving average in the previous year: [Measures].[Sales Amount-Order Retail445-30PrdMvAvgPrevYear]

Avg(
  ParallelPeriod(
      [Order Date Dimension].[Order Retail445].[Order ReportIng_Year],
      1,
      [Order Date Dimension].[Order Retail445].CurrentMember
      ).Lag(29):
  ParallelPeriod(
      [Order Date Dimension].[Order Retail445].[Order ReportIng_Year],
      1,
      [Order Date Dimension].[Order Retail445].CurrentMember
      ),
  [Measures].[salesamount1]
   )

Workbooks

Figure 9. Tableau workbook for use case 11 against 30PrdMvAvg

/public/images/30prdmvavg_prdmvavgprevyear_tableau.png

Figure 10. Excel workbook for use case 11 against 30PrdMvAvg

/public/images/30prdmvavg_prdmvavgprevyear_excel.png

30-period moving average percent change compared to the previous year's

This use case compares the growth in percentage of the 30-period moving average, compared to that of the same period in the previous year.

Case 12

Calculated measure for 30-period moving average growth between years : [Measures].[Sales Amount-Order Retail445-30PrdMvAvgPrevYearGrowthPct]

(
    [Measures].[Sales Amount-Order Retail445-30PrdMvAvg]
    -
    [Measures].[Sales Amount-Order Retail445-30PrdMvAvgPrevYear]
    )
/
[Measures].[Sales Amount-Order Retail445-30PrdMvAvgPrevYear]

Workbooks

Figure 11. Tableau workbook for use case 12

/public/images/30PrdMvAvgPrevYearGrowthPct_tableau.png

Figure 12. Excel workbook for use case 12

/public/images/30PrdMvAvgPrevYearGrowthPct_excel.png