Summarize table wrong measure results

[enzo88]

Hi,

i have an issue with the result of a summarize table, I am trying to calculate the "Baseline Budget Production" price, which is the price of the previous year (inflated by a certain %), based on the MODE price measure.

In this example, everything works fine

Mode Production	Baseline Budget Production	PROD	Material	Delivery Year
414.619,13	414.619,13	PRODUCTION	A	2019
421.170,43	420.838,42	PRODUCTION	A	2020
425.887,83	427.487,99	PRODUCTION	A	2021
440.410,43	433.127,92	PRODUCTION	A	2022
466.923,48	469.785,81	PRODUCTION	A	2023
484.433,04	487.984,53	PRODUCTION	A	2024
466.923,48	501.136,46	PRODUCTION	A	2025

However, if a year is missing, so the material hasn't been purchased for a certain period, some values are wrong.

I have highlighted them in red, the first should be 564,44*(1+Inflation), the second should be 382,66*(1+inflation)*(1+Inflation).

It appears the formula of the budget price does not aggregate correctly the data in the summarize table.

Mode Production	Baseline Budget Production	PROD	Material	Delivery Year
720,92	720,92	PRODUCTION	B	2019
564,44	765,40	PRODUCTION	B	2021
382,66	382,66	PRODUCTION	B	2022
19,07	19,07	PRODUCTION	B	2024

Here are the formulas:

Mode table Production = SUMMARIZE (

    FILTER ( 'PO Schedule', 'PO Schedule'[Empty_check]=0 ),

    'PO Schedule'[Material],

    'PO Schedule'[Delivery Year],

    "Mode Production", [Mode € (Production)],

    "Baseline Budget Production", [Baseline Budget Price (Prod) €],

    "Baseline Actual Prod",[Baseline Actual Price (Prod) €],

    "PROD", "PRODUCTION"

)



Baseline Budget Price (Prod) € =

VAR yearstart = SELECTEDVALUE('PO Schedule'[Deliv. Date].[Year])

VAR ModeProductionYear1 = CALCULATE([Mode € (Production)], 'PO Schedule'[Deliv. Date].[Year] = yearstart-1)

VAR ModeProductionYear2 = CALCULATE([Mode € (Production)], 'PO Schedule'[Deliv. Date].[Year] = yearstart-2)

VAR ModeProductionYear3 = CALCULATE([Mode € (Production)], 'PO Schedule'[Deliv. Date].[Year] = yearstart-3)

RETURN

    IF(

        ModeProductionYear1 > 0,

        ModeProductionYear1 * (1 + [Inflation]),

        IF(

            ModeProductionYear2 > 0,

            ModeProductionYear2 * (1 + [Inflation]) * (1 + 0.046),

            IF(

                ModeProductionYear3 > 0,

                ModeProductionYear3 * (1 + [Inflation]) * (1 + 0.046) * (1 + 0.017),

                [Mode € (Production)]

            )

        )

    )

[johnbasha33]

@enzo88 

It seems like the issue arises from the calculation of the "Baseline Budget Price (Prod) €" measure when there are missing years in the data. When a year is missing, the measure doesn't aggregate correctly because it relies on specific year-based calculations.

To address this issue, you can modify the measure to handle cases where data for previous years is missing. You can use the LASTNONBLANK function to retrieve the last available price before the current year and then apply the inflation factor.

Here's how you can modify the "Baseline Budget Price (Prod) €" measure:

```DAX
Baseline Budget Price (Prod) € =
VAR CurrentYear = SELECTEDVALUE('PO Schedule'[Delivery Year])
VAR LastYearPrice = CALCULATE([Mode € (Production)], FILTER(ALL('PO Schedule'[Delivery Year]), 'PO Schedule'[Delivery Year] < CurrentYear), LASTNONBLANK('PO Schedule'[Mode Production], [Mode Production]))
RETURN
IF(
NOT ISBLANK(LastYearPrice),
LastYearPrice * (1 + [Inflation]),
[Mode € (Production)]
)
```

This modified measure calculates the "Baseline Budget Price (Prod) €" by finding the last available price from the previous years using the LASTNONBLANK function. If there is no available price from the previous years, it defaults to the current year's price. Then, it applies the inflation factor to the last available price.

This approach should provide more accurate results even when there are missing years in the data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!