DAX Function for Calculating Maximum Value Based on Date Conditions

Overview

This DAX function retrieves the maximum value from the WARTOSC column in the Mieszanie table, where the DATA column does not exceed the DateTime values in the DimDate table.

Table Structure

• Mieszanie:

  • DATA: Column containing date values.
  • WARTOSC: Column containing numeric values.

• DimDate:

  • DateTime: Column containing date values (with time).

Expected Outcome

The result should be a calculated table or measure that returns the maximum WARTOSC for each DateTime in DimDate, constrained by the corresponding DATA in Mieszanie.

DAX Code

Here is the professional-grade DAX code to achieve this requirement:

MaxWartoscByDate = 
    VAR CurrentDateTime = SELECTEDVALUE(DimDate[DateTime])
    RETURN
        CALCULATE(
            MAX(Mieszanie[WARTOSC]),
            Mieszanie[DATA] <= CurrentDateTime
        )

Explanation of the Code

• VAR CurrentDateTime: This variable captures the current DateTime being evaluated from the DimDate table.
• CALCULATE: This function modifies the filter context to compute the maximum WARTOSC.
  • MAX(Mieszanie[WARTOSC]): This retrieves the maximum value from the WARTOSC column in Mieszanie.
  • Mieszanie[DATA] <= CurrentDateTime: This filter condition ensures that only rows with a DATA value less than or equal to the current DateTime are considered.

Input Validation

While DAX doesn't allow explicit input validation in the same manner as traditional programming languages, ensure that:

• Mieszanie[DATA] must be a date type.
• DimDate[DateTime] must be a date type.

Example Use Case

To use this measure in a report:

1. Ensure both Mieszanie and DimDate tables are available in your Power BI model.
2. Create a new measure using the provided DAX code.
3. Drag and drop the DateTime column from DimDate into a visual (e.g., a table or a card).
4. Add the MaxWartoscByDate measure to the same visual to see the maximum WARTOSC for each date.

Conclusion

The provided DAX function efficiently computes the desired maximum value while adhering to good coding practices. This solution can be customized or expanded based on additional requirements using the Enterprise DNA platform for further learning and applications in data analytics.