Ravindra BagaleCourses & study guides मराठी Track your progress

24. Power Pivot and the Data Model

24.8 A Simple Time-Intelligence Measure

By Last updated:

Example first. Total Sales by month: Oct ₹1,005, Nov ₹1,310. Ravindra Bagale wants a "Last Month" column and a "MoM %" column next to it. Two short measures with DATEADD and DIVIDE give Nov: last month ₹1,005, change 30.3%.

Time intelligence — in 30 seconds

What: Time-intelligence functions shift or extend the date filter: DATEADD(Calendar[Date],-1,MONTH) means "the same days, one month earlier". TOTALYTD adds from 1 January to the current date.

Why: Month-on-month and year-to-date numbers are the most asked-for figures in any report. With the Calendar table and two measures they work in every PivotTable, for every city.

Tiny example: Sales LM := CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, MONTH)). For Nov it returns ₹1,005; MoM % = 30.3%.

First look at the data (before → after):

Time intelligence: before and after tables, fictional Blinkit Maharashtra data

Expected result — PivotTable with time-intelligence measures

Month Total Sales (₹) Sales LM (₹) MoM % Sales YTD (₹)
Oct 1,005 (blank) (blank) 1,005
Nov 1,310 1,005 30.3% 2,315
Before table and formula as text

Before — PivotTable: Total Sales by month

Month Total Sales (₹)
Oct 1,005
Nov 1,310

Formula / click path

Sales LM := CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, MONTH))
MoM %    := DIVIDE([Total Sales] - [Sales LM], [Sales LM])
Sales YTD := TOTALYTD([Total Sales], Calendar[Date])

Do it step by step:

  1. Make sure the Calendar table is related to Orders[Date] and marked as the date table (lesson 24.5).
  2. Click Power Pivot › Calculations › Measures › New Measure… and create Sales LM with =CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, MONTH)).
  3. Create MoM % with =DIVIDE([Total Sales] - [Sales LM], [Sales LM]) and set its format to Percentage.
  4. Create Sales YTD with =TOTALYTD([Total Sales], Calendar[Date]).
  5. In the PivotTable put Calendar[Month] in Rows and the four measures in Values.
  6. Add Stores[City] as a slicer and click Pune to see Pune month-on-month.

Nov vs Oct by city (slicer on each city)

City Oct (₹) Nov = Total Sales (₹) Sales LM (₹) MoM %
Pune 540 400 540 -25.9%
Nashik 180 490 180 172.2%
Nagpur 195 160 195 -17.9%
Solapur 90 260 90 188.9%

Why DIVIDE and not "/"? For October, Sales LM is blank (there is no September in the data). [Total Sales]/[Sales LM] would give an error; DIVIDE returns blank instead. The Calendar here covers only October and November to keep the example small; in real work use whole years (see the tip in 24.5). The same pattern is taught in the Power BI course: Power BI course.

Practice task (try it before you open the answer):

  1. Create Sales LM and MoM %.
  2. Use a City slicer and find the city with the biggest fall from Oct to Nov.
  3. Find the city with the biggest rise.
Answer

Biggest fall: Pune, ₹540 → ₹400 (-25.9%). Biggest rise: Solapur, ₹90 → ₹260 (188.9%). Nashik is close behind at 172.2%.

Ravindra Bagale's Tip

Always show the money next to a percentage. "+188.9%" for Solapur sounds huge, but it is ₹90 to ₹260 in this practice data. Percentages on small bases mislead.