Changing Measures using Field Parameters

To create a Time Intelligence DAX formula that can dynamically switch between different measures (such as Total Sales, Quantity, Cost, etc.) based on user-selected field parameters, you can use DAX functions and a Parameter table. Here's a step-by-step guide on how to achieve this:

Step 1: Create a Parameter Table

A Parameter table is a table in your data model that holds the information about the selected measure (sales, profit, quantity, etc.). You can create it in Power BI or any other supported tool. It should have at least two columns: one for the parameter name (e.g., "MeasureName") and another for the corresponding measure column name (e.g., "MeasureColumnName"). Here's an example of what your Parameter table might look like:

MeasureName MeasureColumnName

Step 2: Create a Measure for Dynamic Calculation

You need to create a DAX measure that calculates the desired metric based on the user's selection from the Parameter table. Here's a sample DAX measure formula for YoY Change:

YoY Change = VAR SelectedMeasure = SELECTEDVALUE(Parameter[MeasureColumnName]) RETURN SWITCH( SelectedMeasure, "Total Sales", [Total Sales], "Quantity", [Quantity], "Cost", [Cost], 0 ) - CALCULATE( SWITCH( SelectedMeasure, "Total Sales", [Total Sales], "Quantity", [Quantity], "Cost", [Cost], 0 ), SAMEPERIODLASTYEAR(Calendar[Date]) )

In this example, we use the SWITCH function to dynamically select the appropriate measure based on the value selected in the "MeasureName" column of the Parameter table. We then calculate the YoY change for the selected measure.

Step 3: Create a Matrix Visual

Create a matrix visual in your report where you want to display the dynamic time intelligence measures.

Step 4: Add Parameter to the Matrix Visual

Drag the "MeasureName" column from your Parameter table to the rows or columns of the matrix visual. This will allow users to select the desired measure (e.g., Total Sales, Quantity, Cost) dynamically.

Step 5: Add the Dynamic Measure to the Matrix

Drag the "YoY Change" measure you created in Step 2 to the Values area of the matrix visual.

Now, when users select a measure from the "MeasureName" column in the matrix, the "YoY Change" measure will dynamically calculate and display the YoY change for the selected measure.

Repeat the process for other time intelligence calculations like MoM Change, QoQ Change, etc., by creating similar DAX measures for those calculations.

By following these steps, you can create a dynamic report that allows users to switch between different measures and view their time-based changes using DAX formulas and a Parameter table.