Workshop 3 - Accounting Arquitectures - Module 2

Author

Alberto Dorantes, Ph.D.

Published

August 25, 2026

Abstract
In this workshop we continue with the same business case and dataset, but we now learn about Time Intelligence functions in DAX. We use Time intelligence functions in accounting/finance to calculate growth indicators such as annual % sales increase, annual % gross profit increase. We also learn how to decompose a change in gross profit into the drivers management can actually act on.

0.1 Introduction

In this workshop We learn more sophisticated DAX measures to compute important Key Performance Indicators in the context of Accounting and Finance. More specifically, we learn the function CALCULATE and Time Intelligence Functions to easily calculate Year-over-Year % growth of different indicators.

To illustrate this, we continue with the same Business Case and Data model we used for Workshop 2. Then, you do not need to start a semantic model from scratch. Just open the Data Model and the Report to continue working on them.

0.2 The CALCULATE function

As an information analyst you often need to generate reports and get values from specific metrics from previous periods, so you can identify trends or patterns or compare the performance of the business over time.

DAX includes functionality to allow you to do this kind of analysis using the CALCULATE function. CALCULATE is one of the most important, useful and complex function of the DAX language. We use the CALCULATE function when we want to apply or calculate a specific MEASURE to a specific subset of the data. In general, the syntax for the calculate function is the following:

MYMEASURE = CALCULATE([MEASURE1], filter1, filter2, … , filterN)

I can also write the measure formula as the first parameter of Calculate:

MYMEASURE = CALCULATE(SUM(‘Order Details’[Subtotal]), filter1, filter2, … , filterN)

In this case I am assuming that the measure MEASURE1 exists in my model. Then, I can use the CALCULATE function to apply one or more filters to a specific MEASURE or a function.

For example, imagine I have a table for sales and I already created a measure SALES that is the sum of the column subtotal of the sales table. Imagine that the firm has several stores in several countries, so in the database we have a table for stores and another table for countries. I could create a MEASURE for the sales for México (excluding the sales for the rest of the countries). Then I could define the following measures:

SALES = sum(‘Sales table’[subtotal])

SALESMEXICO = CALCULATE([SALES],‘Country table’[country]=”México”)

In this case I created the measure SALESMEXICO that will always calculate sales for Mexico only.

CALCULATE is the only function that is capable of modifying the filter context and evaluates an expression in that context. The filter context is given by the interaction of the user when he or she selects an item in a slicer.

For the filters I can also use a TIME INTELLIGENCE function as a filter.

In order to work with CALCULATE function and time intelligence functions, you need to have a date table, and correctly add the connections to this table in your data model.

In this example, there is a table called Calendar in the data model.

Make sure you have establish a relationship between the Calendar table and the Sales Table. You have to use the DATE attribute in both tables to do this connection.

Now you can start working with the queries and reports using CALCULATE and Time Intelligence functions.

0.3 Data model

You have to open the same data model we used for Workshop 2. Make sure all the connections are well established. Go to the PowerBI web app (app.powerbi.com), sign in with your Tec account, and open the Database Model and the Workshop 1 Report (informe).

0.4 CHALLENGE 1 - Calculating sales and profit annual growth

Using the same semantic model we used in Workshop 2

Write the following measures. In case the measure is a division of 2 numbers, use the DIVIDE function since it already validates divisions by zero (if the denominator is zero, DIVISION returns a ‘Blank’ result)

  1. NET SALES LY - net sales for the same period last year. Hint: CALCULATE and use SAMEPERIODLASTYEAR('Calendar'[Date]) as filter.

  2. ANNUAL NET SALES % GROWTH - growth versus last year

  3. GROSS PROFIT LY - gross profit for the same period last year

  4. ANNUAL GROSS PROFIT % GROWTH - growth versus last year

  5. GP MARGIN LY - Gross Profit Margin for the same period last year

  6. GP MARGIN DELTA PP - Annual difference in percent points of Gross Margin = ([GP MARGIN] - [GP Margin LY]) * 100.

  7. SALES SHARE - Net Sales (visible Net Sales after any selection) as a % of Total Sales

Here you have to learn about Filter Context. Remember that any measure (aggregation) you write in DAX assumes that it will be calculated “on-the-fly” when you drag the measure in a visualization ‘gadget’ such as table or graph. Then, the calculations are performed according to whatever the user selected by “clicking” any dimension, column or piece of a graph. You can force a measure to always use all the information by the function ALLSELECTED(). Bellow is the way you have to write a dynamic measure for Sales Share:

DIVIDE([Net Sales], CALCULATE([Net Sales], ALLSELECTED())).

In this case, the measure [Net Sales] will be calculated considering any filter the user select, but the CALCULATE[Net Sales], ALLSELECTED()) will calculate the same measure WITHOUT considering the filters in the context.

  1. (OPTIONAL, hard to understand) NET INCOME - EBIT after the corporate tax rate of each country. The rate differs by country, so one multiplication at the grand total is wrong. You have to INTERATE row by row using the SUMX function:
Net Income =
SUMX(
    VALUES( Countries[CountryCode] ),
    [EBIT] * ( 1 - CALCULATE( MAX( Countries[CorporateTaxRate] ) ) )
)

Why do you think that the following DAX formula:

[EBIT] * (1 - AVERAGE(Countries[CorporateTaxRate]))

returns a different - and wrong - number?

0.5 CHALLENGE 2 - “Commercial efficiency” Dashboard

Create a page named 2-COMMERCIAL EFFICIENCY containing:

  1. A table of sales representatives (last name only) with Net Sales, Orders, Avg Sale per Order and Avg Discount Rate, sorted by Discount Rate descending.

  2. A matrix of Mkt Intensity by Country (rows) × Division (columns)

  3. A Pareto view of customers: Net Sales by customer sorted descending, with % of Total Net Sales and a cumulative share.

  4. A stacked bar or donut of Net Sales by Channel and by CreditRating.

  5. Slicers: Country, Year, Division.

Then answer:

Q1. The company-wide discount rate is around 7%. Two representatives are far above it. Identify them, quantify the gross profit their excess discount consumed over the two years, and state whether the extra discount bought proportionally more sales.

Q2. One country-division combination spends dramatically more on marketing relative to its sales than any other. Identify it, quantify the gap versus the company average, and estimate the SG&A that would be released by bringing it back to that average.

Q3. What share of net sales comes from the top 10 customers? What risk does that create and what would you monitor monthly?

Q4. Which month is the seasonal peak and which the trough? Name one operational decision - inventory, staffing or promotion calendar - that this pattern should change.

0.6 (OPTIONAL) CHALLENGE 3 - The gross profit bridge

This is the analytical core of the workshop, and it is the DuPont logic applied to a different ratio: decompose a change into its drivers instead of merely reporting it.

Gross profit changed between 2024 and 2025 for exactly three reasons: we sold a different quantity, at a different average price, with a different average cost.

The decomposition is exact:

\[\Delta GP = \underbrace{(Q_1-Q_0)(P_0-C_0)}_{\text{volume}} + \underbrace{(P_1-P_0)Q_1}_{\text{price}} + \underbrace{-(C_1-C_0)Q_1}_{\text{cost}}\]

Create these measures (... LY versions use SAMEPERIODLASTYEAR as in the previous challenge):

Volume Effect = ( [Units Sold] - [Units Sold LY] ) * ( [Avg Price LY] - [Avg Cost LY] )
Price Effect  = ( [Avg Price]  - [Avg Price LY]  ) *   [Units Sold]
Cost Effect   = - ( [Avg Cost] - [Avg Cost LY]   ) *   [Units Sold]
GP Change     = [Gross Profit] - [Gross Profit LY]

Build a waterfall chart on a page named 3-GP BRIDGE, going from Gross Profit 2024 to Gross Profit 2025 through the three effects, plus a matrix of the same three effects by Division and by Product.

Q5. Do the three effects add up exactly to GP Change at company level? Now build the same decomposition by product and sum it. The two totals differ. Explain why - the missing piece has a name in management accounting - and state which of the two versions you would put in front of the CFO.

Q6. Which single effect is responsible for gross profit stagnating? What share of that effect comes from the two products you identified in Q3?

0.7 CHALLENGE 4 - Pin a dashboard and write the memo

  1. Pin your most important visuals from the three report pages to a Dashboard named AFI Executive Dashboard - <your matricula>.

    Vocabulary that only exists in the web version: a report is a multi-page interactive artifact bound to one semantic model; a dashboard is a single canvas of pinned tiles that may come from several reports. Executives look at dashboards; analysts work in reports.

  2. Write a one-page executive memo to the CFO with:

    • Your diagnosis of why gross profit stagnated, quantified with the bridge.
    • One SMART objective for next year (Specific, Measurable, Achievable, Relevant, Time-bound).
    • One strategy, the assumption it rests on, and its expected impact on gross profit in USD.
    • The limitations of your analysis: at minimum what SG&A excludes, what you did with each group of missing values, and the fact that standard cost is not actual cost.

A memo saying “margins fell, we should improve them” is worth nothing. A memo saying “gross profit grew only USD X (+X%) because a USD X cost headwind absorbed a USD X volume gain; X% of that headwind comes from two SKUs whose unit cost rose X% while their price was left unchanged; repricing them to their 2024 margin recovers approximately USD X at current volumes, assuming demand elasticity below Y” is worth being hired for. Fill in every italicized number from your own model.

0.8 Deliverables

Submit through Canvas:

  1. A .pbix file (File → Download this file).
  2. A PDF or Word document containing: screenshots of the report pages and of the pinned dashboard; your written answers to the questions in the challenges and the executive memo.

0.9 Evaluation rubric

Grading rubric
Criterion Weight What earns full credit
Verification and missing values 10% All five integrity checks run and reported; each group of blanks treated and justified; correct data types
Semantic model 25% Nine relationships with correct cardinality and direction; calendar marked as date table; technical columns hidden; MonthName sorted by MonthNumber
DAX 25% Calculated columns vs measures used correctly; every ratio a DIVIDE of two sums; the five self-written measures and the three bridge effects correct
Dashboards 20% Three pages complete, readable, formatted, slicers functional, sorted meaningfully
Insights and memo 20% Q1-Q10 answered with numbers; bridge interpreted correctly; SMART objective; strategy with stated assumptions; limitations acknowledged

Automatic deductions. Any ratio computed as an arithmetic average of a ratio column: -10%. Any figure consolidated without currency conversion: -10%. A report with no formatting - raw decimals, default titles, no thousands separators: -5%.

1 Appendix - DAX quick reference for this workshop

DAX patterns you will need
Task Pattern
Safe ratio DIVIDE( [Numerator], [Denominator] )
Bring a column from the one side RELATED( Dim[Column] )
Sum from the many side SUMX( RELATEDTABLE(Fact), Fact[Col] )
Count distinct DISTINCTCOUNT( Sales[OrderID] )
Same period last year CALCULATE( [Measure], SAMEPERIODLASTYEAR('Calendar'[Date]) )
Year-over-year % DIVIDE( [Measure] - [Measure LY], [Measure LY] )
Ignore all filters CALCULATE( [Measure], ALL(Table) )
Share of the visible total DIVIDE( [Measure], CALCULATE([Measure], ALLSELECTED()) )
Running total CALCULATE( [Measure], FILTER(ALLSELECTED(Dim), Dim[Rank] <= MAX(Dim[Rank])) )
Top N by a measure TOPN( 10, VALUES(Dim[Key]), [Measure], DESC )
Weighted average across groups DIVIDE( SUMX(T, T[Value]*T[Weight]), SUM(T[Weight]) )
Iterate over a dimension SUMX( VALUES(Dim[Key]), <expression> )