Moth to day - Adjustes

This is the code that works as specified:

IF(
    ISFILTERED('dCalendario'[DataBase]),
    ERROR("erro"),
    VAR _DateValue =
        // Create a virtual table with a row per date and each cumulated forecast value,
        // to then pick the overall maximum forecast value,
        // to then pick the minimum date with this maximum forecast value,
        // to then return no more values after that date (i.e. any period, e.g. month, that consists only of dates after this date)
        CALCULATETABLE (
            ADDCOLUMNS (
                SUMMARIZECOLUMNS (
                    'tbl_tasks'[Valor]
                ),
                "@MonthlyValue",
                VAR _LastDate = CALCULATE ( MAX ( 'dCalendario'[DataBase].[Date] ) )
                RETURN
                CALCULATE (
                    [_Qty.baseline - Forecast],
                    ALLSELECTED('dCalendario'),
                    'dCalendario'[DataBase].[Date] <= _LastDate
                )
            ),
            ALLSELECTED ( 'dCalendario' )
        )
    // get the maximum forecast value
    VAR _MaxValue = MAXX ( _DateValue, [@MonthlyValue] )
    // get the first month with the maximum forecast value
    VAR _EarliestDateWithMaxValue = MINX ( FILTER ( _DateValue, [@MonthlyValue] = _MaxValue ), [Valor] )
    // get the first date of the selected period, e.g. month
    VAR _FirstDateInContext = MIN ( 'dCalendario'[DataBase].[Date] )
    RETURN
    IF (
        _FirstDateInContext <= _EarliestDateWithMaxValue,
        CALCULATE(
            [_Qty.baseline - Forecast],
            'dCalendario'[DataBase].[Date], // only include values from dates that exist in the fact table ([Valor] column) and in the date table, 
                                            // i.e. no values for before jan 2019 (date table), and no values for before nov 2022 (first month with date on or after jan 2019 with data)
            'dCalendario'[DataBase].[Date] <= MAX('dCalendario'[DataBase].[Date]),
            ALLSELECTED ( 'dCalendario' )
        )
    )
)

Thoughts:

• If you load your own date table anyway, you can mark it as date table and turn off auto datetime. This makes the behavior of DAX code in the model easier to understand because no more hidden auto-date-tables impact the behavior. Be aware that if you change this after you have written your DAX measures you need to change the DAX measures, e.g. change 'Date'[Date].[Month] (referenece to the Month column of the auto-date-table which then no longer exists) to 'Date'[Month] (reference to the Month column of your date table).
• Your date table starts 2019, your data starts 2015, and in the measure you make sure that only values from dates in the date table are included in the calcualtion. Best practice would be to load a complete date table, covering all your facts, write a universal measure, and then apply a date filter in the report as needed. Thus your peak curently shows 43, instead of 46 if all values since 2015 were included.
• You could simplify this code if the following assumptions apply, which I'm just guessing:
  • The actual requirement is not to end showing data at the maximum cumulative value but at the last month with data for the measure. At least this is the way more popular requirement. Then calculating the date at which to stop showing data could be done with less code and would be faster. The measure now stops showing data at the peak, even if the cumulative value goes down afterwards (according to requirements).
  • Explicitly specifying that all values that go into the cumulative total are positive. Then it would also be clear that the last date with a non-zero value is also the last date at which to show data.

BR

Martin