0
0
Power BIbi_tool~10 mins

Why time-based analysis drives decisions in Power BI - Formula Trace Breakdown

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Sample Data

Daily sales data for the first five days of January 2024.

CellValue
A1Date
B1Sales
A22024-01-01
B2100
A32024-01-02
B3150
A42024-01-03
B4120
A52024-01-04
B5130
A62024-01-05
B6170
Formula Trace
Total Sales Last 3 Days = CALCULATE(SUM(Sales[Sales]), DATESINPERIOD(Sales[Date], MAX(Sales[Date]), -2, DAY))
Step 1: MAX(Sales[Date])
Step 2: DATESINPERIOD(Sales[Date], 2024-01-05, -2, DAY)
Step 3: SUM(Sales[Sales]) filtered by dates [2024-01-03, 2024-01-04, 2024-01-05]
Step 4: CALCULATE(...)
Cell Reference Map
    A          B
1 | Date     | Sales |
2 |2024-01-01|  100  |
3 |2024-01-02|  150  |
4 |2024-01-03|  120  |
5 |2024-01-04|  130  |
6 |2024-01-05|  170  |

References:
- MAX uses column A (Date)
- SUM uses column B (Sales)
- DATESINPERIOD uses column A (Date) for filtering
The formula uses the Date column to find the latest date and create a 3-day window, then sums Sales values in that window.
Result
    A          B               C
1 | Date     | Sales | Total Sales Last 3 Days |
2 |2024-01-01|  100  |                       |
3 |2024-01-02|  150  |                       |
4 |2024-01-03|  120  |                       |
5 |2024-01-04|  130  |                       |
6 |2024-01-05|  170  |          420          |
The calculated measure 'Total Sales Last 3 Days' shows 420 on the latest date row, representing the sum of sales from Jan 3 to Jan 5.
Sheet Trace Quiz - 3 Questions
Test your understanding
What date does the MAX function find in the formula?
A2024-01-03
B2024-01-05
C2024-01-01
D2024-01-02
Key Result
CALCULATE with DATESINPERIOD sums values over a dynamic recent date range.