Moth to day - Adjustes

[Alph4]

I would like to adjust this measure so that when it identifies the highest accumulated value, it does not replicate that value until the end of the 'dCalendario' table.

File exemple: 
https://www.dropbox.com/s/ifm5octlpkq5wng/Forecast_Comunnity.pbix?dl=0

IF(
    ISFILTERED('dCalendario'[DataBase]),
    ERROR("erro"),
    CALCULATE(
        [Qty.Liabilities - Forecast],
        'dCalendario'[DataBase].[Date],
        ALL('dCalendario'[DataBase].[Mês]),
        'dCalendario'[DataBase].[Date] <= MAX('dCalendario'[DataBase].[Date])
    )
)

For example, when the measure identifies that the highest value is 32, it will only display values up to: 2023 November 32

Year Month FORECAST

2022	April	3
2022	May	4
2022	June	4
2022	July	5
2022	August	5
2022	September	5
2022	October	5
2022	November	5
2022	December	5
2023	January	5
2023	February	6
2023	March	9
2023	April	9
2023	May	16
2023	June	16
2023	July	19
2023	August	26
2023	September	30
2023	October	30
2023	November	32
2023	December	32
2024	January	32
2024	February	32
2024	March	32
2024	April	32
2024	May	32
2024	June	32
2024	July	32
2024	August	32
2024	September	32
2024	October	32
2024	November	32
2024	December	32

 @tamerj1  @Jihwan_Kim  @Martin_D 

[Martin_D]

@Alph4 would you mind providing the file or an ananomyzed sample file?

[Alph4]

https://www.dropbox.com/s/ifm5octlpkq5wng/Forecast_Comunnity.pbix?dl=0

Are there other way to share the file here ? 

[Martin_D · Accepted solution]

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

[Alph4]

Thank you, in the given example, it worked perfectly, but in the actual case, there are still some errors,. My objective is to create an S-curve for this table, an S-curve for the planned values based on the "End" column, an S-curve for the projected values based on the "End" column, and a curve for the actual values up to the current day. Could you suggest another approach based on this model that I provided, keeping in mind that this model only has one project? In the original database, I have a column that identifies the projects, and the idea is that when applying a filter on the project name, it would display the curves for each project. 

[Martin_D]

With some shortcomings, you can create an s-curve from your data like this. You can load a Projekt column, then the solution works for multiple projects as well.
S-curve

See attached file for solution.
The two major shortcoming are:

1. With your data you cannot distinguish between tasks of different size. I'd suggest to load absolute data in hours or currency, then you can calculate true progress, not just based on task count.
2. With your data your s-curve makes significant jumps, because you have only on value per task (and attribute) and not one value per task and day/week/month (whatever you want to report on) traking the progress periodically. I'd suggest to load a snapshot of all tasks and their progress every day, week, or month, depending on your reporting needs, per project as long as the project is in progress. Of course you need to collect this data first, this is not just a transformation of the data in the current file.

BR

Martin