Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor


Frequent Visitor


The behavior you're describing with the `Max_Startdatum` variable becoming empty for months where there's no data is expected in Power BI. Variables in DAX are evaluated within the filter context they are used in. When you apply a filter to a visual, such as a month filter, it impacts the evaluation context for DAX calculations. In this case, when there's no data for a specific month for a given project, the `MAX('Booked_Times'[Startdatum])` calculation returns an empty result, and that propagates to your `Max_Startdatum` variable.

If you want to maintain a constant `Max_Startdatum` for a project even when there's no data for certain months, you can use a technique called "earlier reference" to capture the value once for each project and use that value throughout your calculations. Here's how you can modify your `Max_StartDatum` variable:

Max_StartDatum =
VAR ProjectStart = CALCULATE(
EARLIER(ProjectStart), -- Use the previously captured value
ProjectStart -- Use the value for the current project

In this modified calculation, `EARLIER` captures the `ProjectStart` value for the project when there is data and uses it throughout the calculation, even when there is no data for some months.

This should ensure that `Max_Startdatum` remains constant for each project regardless of whether there is data for all months or not. Please test this modification to see if it meets your requirements for the project's constant start date.

This sounded just like the solution because it is exactly what I want. However, I get an error message saying EARLIER/EARLIEST refer to an earlier row context which doesn't exist. What is that supposed to indicate?

Community Champion
Community Champion

It looks like you're trying to use the `Max_Startdatum` variable as a filter condition for your chart, but you're encountering issues with the display of months on the x-axis when using this variable. You've also noticed that when you hardcode the date as `DATE(2023,9,1)`, it works correctly. The problem might be related to how the `Max_Startdatum` variable is computed.

Here's a potential explanation for the issue:

1. Data Type Mismatch:
- `Max_Startdatum` is calculated using `DATEVALUE`, which converts a date in text format to a date value.
- If the format of the date in your 'Booked_Times'[Startdatum] column is not consistent or there are data quality issues, it could lead to unexpected results when converting to a date.
- The hardcoded `DATE(2023,9,1)` is a date value, so there's no ambiguity in its format.

To troubleshoot this issue, you can try the following:

1. **Check Data Quality:**
- Ensure that the 'Booked_Times'[Startdatum] column contains consistent and valid date values.

2. **Display the `Max_Startdatum` Variable:**
- Create a card visual to display the value of `Max_Startdatum` and compare it with the hardcoded `DATE(2023,9,1)`. This will help verify if there are any differences in their formats.

3. **Debugging:**
- To debug the issue further, you can add temporary measures to your visualizations to display intermediate results. For example, create measures to display the month and year components of `Max_Startdatum` and `DATE(2023,9,1)` separately. This can help identify any discrepancies.

Here's an example of how you can create measures to display the month and year components:

Max_Startdatum_Month = MONTH(Max_Startdatum)
Max_Startdatum_Year = YEAR(Max_Startdatum)

After adding these measures, you can add them to your visuals to see the month and year for `Max_Startdatum` and `DATE(2023,9,1)` separately. This should provide more insight into any differences between the two.

By following these steps, you should be able to identify the root cause of the issue and determine why `Max_Startdatum` is not producing the expected results for your chart.

Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.