Unless you work with DAX regularly, remembering the correct syntax to write functions can be tricky. If you find yourself searching for how to write key financial DAX functions, you’re not alone.
This blog covers five essential DAX functions you might need when creating your financial reports.
Key financial reporting metrics often include:
- Actual Month, Budget Month, Month Variance, Month Variance %.
- Actual YTD, Budget YTD, YTD Variance, YTD Variance %.
- Budget Remaining or Target Remaining.
- FY (Full Year) Budget.
- Actual LY (Last Year).
There are five key Dax Functions required to achieve the list of measures required.
Calculate (Sum)
Calculate (Sum) is the explicit measure that defines the column that you intend to sum for the purposes of your reporting. For example, the calculate sum function for reporting the Actual measure from General Ledger transactions may look like:
[Actual] = Calculate(sum(GeneralLedgerTransaction[Amount]))
where “GeneralLedgerTransaction” is the Table and [Amount] is the column with the values in number format that you would like to sum.
The Calculate Sum measure would be used for calculating the Month actual or Budget actual. Budget actual would reference the budget specific table and column.
[Budget] = Calculate(sum(BudgetLedgerTransaction[Amount]))
where “BudgetLedgerTransaction” is the Table and [Amount] is the column with the values in number format that you would like to sum.
Budget variance measure would be the result of the subtraction of the Actual measure from the Budget Measure
where [Budget Variance] = [Budget] – [Actual]
Divide
The divide function provides for the division required for the % Variance movement. The DAX divide function also accounts for any N/A’s or where there are no calculations.
The YTD variance DAX is called a nested DAX. Nested meaning the DAX refers to the output of other DAX measures. The example below has the [YTD Variance] DAX measure divided by the [Budget YTD] DAX calculation. The reference to 0 is the default result where a division is not calculable, replacing n/a results with 0.
YTD Variance % = DIVIDE([YTD Variance],[Budget YTD],0)
TotalYTD
The Total Year to Date [TotalYTD] function provides the date parameters to calculate a YTD sum DAX. The example below provides for the YTD calculation in reference to a DAX called [Actual]. The [Actual] DAX nested in the example is a calculate(sum(Fact[Amount])) DAX. The 'Date'[Date]reference provides the Table and column from which the date reference will occur. The [ALL] function references all dates in a selection, whilst “4/30” [April 30] provides for the end date of a YTD calculation.
Actual YTD = TOTALYTD(CALCULATE([Actual]),'Date'[Date], ALL('Date'[Date]),"4/30")
Same period last year
The same period last year DAX [sameperiodlastyear] function acts as the filter reference that automatically looks at the prior year reference for the defined Date filter. In the example below the SAMPERIODLASTYEAR date reference of 'Date'[Date] is referring to a table named ‘Date’ and column [Date] from the ‘Date’ table to calculate the “same period last year”.
In the example below the DAX measure calculates [Actuals] with the same period as references on the reporting page by any filters on the Date table for the current year -1.
Actual LY = CALCULATE([Actuals],SAMEPERIODLASTYEAR('Date'[Date]))
ABS
The ABS function provides the “absolute value” of a calculation. The absolute value meaning a number without its positive or negative sign.
The ABS function is particularly useful when the default Sales values (or credits) from an accounting system is represented as a negative. Often sales are represented by a negative numeric number. In this circumstance the ABS function will represent the value as a positive number eliminating the negative sign.
The example below provides for the Actual = ABS([Actuals])
Note: These are just generic examples of database table naming conventions. The actual DAX for your scenario will depend on your data modelling.
