Cumulative Sum Functions over Time

Cumulative summing, whether in the form of Month to Date or Year to Date, has never been easier in Power BI. It seems to me that currently, DAX offers some functions that are simpler than Excel. So, how to develop cumulative sums over time? Several DAX functions come to the rescue, and today we’ll discuss them.
Let’s go back to our test dataset, Financial Sample (available in Power BI).
In the report, a new visualization named “Sales in time” has appeared, presenting the sales distribution over time. The formula “Current Sales,” known from the previous post, shows the sales for the current month. Below is a reminder of the measure:
_CurrentSales =
SUM(
fFinancials[Sales]
)
I have disabled the relationships between date filters and the monthly chart. The YTD Sales series shows the cumulative sales value for the current year, while QTD Sales shows the cumulative sales value for the quarter. As you will notice, each subsequent month contains the sum of the previous months plus the current one in the given year/quarter. How to achieve this? DAX offers a set of specific time-summing functions:
  • TOTALMTD – cumulative sum of values for the month
  • TOTALQTD – cumulative sum of values for the quarter
  • TOTALYTD – cumulative sum of values for the year
Since our dataset contains monthly data, I limited myself to using two measures (QTD and YTD), although their development is identical. What does the TOTALYTD function look like from the inside?
TOTALYTD =
(
<expression>,
<dates_column>,
[filter],
[end_of_year]
)
In the place of the expression, we enter the value we want to cumulate (usually the sum). The next argument is to indicate the date column we will use to cumulate values. The filter is an additional (optional) argument. The end_of_year, although optional, can be useful – in financial reporting, where the fiscal year ends in a month other than December, we can indicate which month marks the end of the fiscal year. In the case where the fiscal year ends in June, my formula would look like the one below:
_TotalYTDSales =
TOTALYTD(
[_CurrentSales],
'Calendar'[Date],
ALL('Calendar'[Date]),
"6/30"
)
The effect is shown in the screenshot below:
Time functions in DAX are quite intuitive, and basic transformations do not require advanced knowledge of DAX. Soon on the blog, we will discuss more complex cases of cumulative values.

Interesting article? Share further!

Leave A Comment