Cumulative sales overview

Reports often require that in addition to the usual review of the sales we create running total, i.e. to track cumulative growth of sales from beginning of the month, quarter or year. For this purpose we use the Time Intelligence functions DATESMTD, DATESQTD and DATESYTD by which we can track cumulative sales growth since the beginning of the given period. If you want to find out more please read the recipe that follows…

Let us go back to the example that represents sales within a chain of retail stores. In the Model we have added and connected tables Artikli (Items), Lokacije (Locations) and Transakcije (Transactions). Also we have added Calendar table and connected it to table Transakcije. We created a measure that represents sum of all quantities sold:

UkupnaProdaja:=SUM(Transakcije[KOL])

Syntaxes of functions that are used for cumulative sales overview are:

DATESMTD (<date column>)

DATESQTD (<date column>)

DATESYTD (<date column>)

If we nest them within CALCULATE, we can write expressions that track sales from beginning of month or a year:

ProdajaMTD:=CALCULATE(SUM(Transakcije[KOL]),DATESMTD(‘Calendar'[Date]))

ProdajaYTD:=CALCULATE(SUM(Transakcije[KOL]),DATESYTD(‘Calendar'[Date]))

When we move these measures into Power Pivot report we can track sales growth, running total from start of month or running total from start of year.

Same effect could have been accomplished with these functions:

TOTALMTD (<expression>,<date column>)

TOTALQTD (<expression>,<date column>)

TOTALYTD (<expression>,<date column>)

Only difference is that these functions can work independently from function CALCULATE.