DAX Function: Year-To-Date Calculation with Total for Previous Years

Overview

This DAX function calculates the Year-To-Date (YTD) value for the selected year. For all previous years, it returns the total value instead of YTD.

Function Definition

YTD_Or_Total =
VAR SelectedYear = SELECTEDVALUE(DimDate[Year])
VAR CurrentYear = YEAR(TODAY())

RETURN
    IF(
        SelectedYear < CurrentYear,
        CALCULATE(SUM(FactSales[Amount]), DimDate[Year] = SelectedYear),
        CALCULATE(
            SUM(FactSales[Amount]),
            DATESYTD(DimDate[Date])
        )
    )

Breakdown of the Code

• Variables:

  • SelectedYear: Captures the currently selected year from the 'DimDate' table.
  • CurrentYear: Retrieves the current year dynamically using TODAY().

• Return Logic:

  • An IF statement determines whether the SelectedYear is less than CurrentYear.
    • If True (Selected Year is in the Past):
      • Calculates the total amount for the selected year using CALCULATE and filters it by DimDate[Year].
    • If False (Current Year or Future Year):
      • Uses DATESYTD to get Year-To-Date values for the current year.

Input Validation

DAX inherently manages context through visual filters. However, ensure that:

• The date column (DimDate[Date]) is properly formatted as a date.
• The FactSales[Amount] column exists in your model.

Comments

• The function leverages the power of context transition via CALCULATE.
• Using SELECTEDVALUE allows for dynamic year selection in reports or dashboards.

Code Usage Example

Assuming you have a Power BI report with a slicer that filters years using the DimDate[Year] column, you can use this measure to dynamically calculate YTD values for the current selected year while showing the total for any past year.

1. Create a slicer for years using DimDate[Year].
2. Add a card or visual to display the measure YTD_Or_Total.

This measure will adjust based on user selection, enhancing the analytical capabilities of your report.

Conclusion

This DAX function provides a robust solution for displaying YTD values alongside totals for previous years, making it ideal for year-over-year analytics in dashboards and reports. For further learning on advanced DAX techniques, consider exploring courses on the Enterprise DNA Platform.