Create an ATD (all-to-date) DAX measure to show all time evolution

By Dries on 20/10/2015

If you know DAX then you will probably know the YTD (year-to-date) time intelligence function DATESYTD().  If you want to do something similar, but for all-time evolution instead of year evolution, you have to write a measure for that because an ATD (all-to-date) time intelligence function does not exist.  No worries however, creating a measure like that is simple.

Let’s start with a simple measure that calculates the revenue (‘omzet’ in Dutch) from invoices within the current context.

Omzet Excl taksen in verkoop facturen:=calculate(sum('Verkoop Factuur'[Verkoop Factuur BTW Basis Bedrag]))
Tags van Technorati:

For the record: it is not necessary to explicitely put the calculation in a CALCULATE() function because with a DAX measure there is an implicit CALCULATE().  I mostly do this anyway for readability.  Now, let’s create the measure I will use for the rest of the exercise.

Omzet Excl taksen uit verkoop facturen min creditnotas:=[Omzet excl taksen in verkoop facturen]-[Omzet excl taksen in verkoop creditnotas]

Now we can use the measure in an Excel PivotTable.

image

For every year and quarter, the revenue is reported and the measure returns the revenue for the context we asked: revenue from the beginning of the reporting period (quarter) until the end of the reporting period.

Now let’s create a YTD variant of the measure.

YTD Omzet Excl taksen uit verkoop facturen min creditnotas:=calculate
(
    [Omzet Excl taksen uit verkoop facturen min creditnotas];
    DATESYTD
     (
        'Datum - Document'[Document Dag]
    )
)

Actually, what the DATESYTD() function does is changing the context from “from the beginning of the reporting period until the end of the reporting period” to “from the beginning of the year of the reporting period until the end of the reporting period”.  If we extend the PivotTable with this new measure, we get the expected result.

image

Now, if we want to create an ATD (all-to-date) variant for the measure, we have to program a logic in the DAX measure similar to the logic of the DATESYTD() function.  The logic we want for the ATD variant is “from the beginning of times until the end of the reporting period”.  We have to explicitly change the filter context in our measure, and we do it like this.

ATD Omzet Excl taksen uit verkoop facturen min creditnotas:=calculate
(
    [Omzet Excl taksen uit verkoop facturen min creditnotas];
    filter
    (
        all('Datum - Document');
        'Datum - Document'[Document Dag] <= LASTDATE(values('Datum - Document'[Document Dag]))
    )
)

We change the context in the filter argument of the CALCULATE() function using a FILTER() function.  within the FILTER() function we first remove all filters from our ‘date’ dimension using the ALL() function and then filter the ‘date’ dimension for the dates that are lower or equal to the LASTDATE() of the reporting period. If we extend the PivotTable with this new measure, we get the expected result.

image

Now we can also create a nice looking Excel PivotChart that shows us the all time revenue evolution (the blue bars) together with the revenue per reporting period (the grey line).

image

I will be posting more DAX measure examples, so please come back for more later.

Power BI
DAX
Excel
Power Pivot
Author