SOLVED

[andytan0051]

a

[123abc]

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:

```DAX
Max_StartDatum =
VAR ProjectStart = CALCULATE(
MAX('Booked_Times'[Startdatum]),
ALL('Booked_Times'),
VALUES('Booked_Times'[Arbeitsauftrag])
)
RETURN
IF(
ISBLANK(ProjectStart),
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.

[andytan0051]

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?

[123abc]

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:

```DAX
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.