Add an average of each Product for the last 3 available Datas.

[MrBrownn]

Hello everyone,

I've built a calculated table Named 'Forcast_Table" that merges actual and forecast data to give the user the option for displaying or not a forecast until the end of the year.

Result of an exemple of the table as follows :

Year	Month	Product	Categorie	State	Total
2023	1	Product1	A	Reel	0,815621764
2023	1	Product2	A	Reel	2,298259863
2023	2	Product1	A	Reel	7,563663234
2023	3	Product1	A	Reel	1,486017377
2023	3	Product2	A	Reel	6,973025905
2023	4	Product1	A	Reel	2,936901503
2023	4	Product2	A	Reel	1,566218564
2023	4	Product3	A	Reel	9,793628798
2023	5	Product1	A	Reel	3,723159548
2023	5	Product2	A	Reel	3,652480619
2023	5	Product3	A	Reel	1,108706899
2023	6	Product1	A	Reel	9,037918128
2023	6	Product2	A	Reel	8,793033488
2023	6	Product3	A	Reel	4,46072489
2023	7	Product1	A	Reel	9,566435301
2023	7	Product2	A	Reel	7,605838191
2023	8	Product1	A	Prediction	Exemple here : AVG of 9,566435301 & 9,037918128 & 3,723159548)
2023	8	Product2	A	Prediction
2023	8	Product3	A	Prediction
2023	9	Product1	A	Prediction
2023	9	Product2	A	Prediction
2023	9	Product3	A	Prediction
2023	10	Product1	A	Prediction
2023	10	Product2	A	Prediction
2023	10	Product3	A	Prediction
2023	11	Product1	A	Prediction
2023	11	Product2	A	Prediction
2023	11	Product3	A	Prediction
2023	12	Product1	A	Prediction
2023	12	Product2	A	Prediction
2023	12	Product3	A	Prediction
2023	1	Product1	B	Reel	17,72045003
2023	3	Product2	B	Reel	43,24592979
2023	4	Product1	B	Reel	81,56559688
2023	5	Product3	B	Reel	97,98441339
2023	6	Product1	B	Reel	79,95048349
2023	6	Product2	B	Reel	43,98700767
2023	6	Product3	B	Reel	48,04309131
2023	7	Product1	B	Reel	91,17372428
2023	7	Product2	B	Reel	60,25579445
2023	8	Product1	B	Prediction
2023	8	Product2	B	Prediction
2023	8	Product3	B	Prediction	And here (AVG of 48,04309131 & 97,98441339)
2023	9	Product1	B	Prediction
2023	9	Product2	B	Prediction
2023	9	Product3	B	Prediction
2023	10	Product1	B	Prediction
2023	10	Product2	B	Prediction
2023	10	Product3	B	Prediction
2023	11	Product1	B	Prediction
2023	11	Product2	B	Prediction
2023	11	Product3	B	Prediction
2023	12	Product1	B	Prediction
2023	12	Product2	B	Prediction
2023	12	Product3	B	Prediction

My need is to add on each row of prediction state the average of the available last 3 months datas to each product and categorie.

i tried a Window function on a calculated column but without success.

Any suggestion ?
thanks in advance for your help.

[MrBrownn]

New informations :
I used "UNION" between "REEL" table and "Prediction" table, but the prediction table is just a calculated table that contains all the products from the next month without data.

For example, if we had real data up to April, then the Prediction table would list all the products from May to December.

[Anonymous · Accepted solution]

 Hi @MrBrownn 

You can use the following DAX to achieve the result:

Average Last 3 Months = IF('Forecast_Table'[State] = "Reel",BLANK(),

CALCULATE(

    AVERAGE('Forecast_Table'[Total]),

    FILTER(

        ALL('Forecast_Table'),

        'Forecast_Table'[Year] = EARLIER('Forecast_Table'[Year]) &&

        'Forecast_Table'[Product] = EARLIER('Forecast_Table'[Product]) &&

        'Forecast_Table'[Categorie] = EARLIER('Forecast_Table'[Categorie]) &&

        'Forecast_Table'[Month] >= EARLIER('Forecast_Table'[Month]) - 3 &&

        'Forecast_Table'[Month] < EARLIER('Forecast_Table'[Month]) &&

        'Forecast_Table'[State] = "Reel"

    )

)

)

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[MrBrownn]

Good morning @Anonymous,
Thanks a lot for your reply, it's working for the first month after data but the next ones it's takes 2 average months for exemple on Month 8, Product1 A, it's an average only of Month 6,7 of the product

[Anonymous]

Hi @MrBrownn 

You can use the new DAX to achieve the result:

Average Last 3 Months = IF('Forecast_Table'[State] = "Reel",BLANK(),IF('Forecast_Table'[Categorie] = "A",
CALCULATE(
    AVERAGE('Forecast_Table'[Total]),
    FILTER(
        ALL('Forecast_Table'),
        'Forecast_Table'[Year] = EARLIER('Forecast_Table'[Year]) &&
        'Forecast_Table'[Product] = EARLIER('Forecast_Table'[Product]) &&
        'Forecast_Table'[Categorie] = EARLIER('Forecast_Table'[Categorie]) &&
        'Forecast_Table'[Month] >= EARLIER('Forecast_Table'[Month]) - 3 &&
        'Forecast_Table'[Month] < EARLIER('Forecast_Table'[Month]) &&
        'Forecast_Table'[State] = "Reel"
    )
),CALCULATE(
    AVERAGE('Forecast_Table'[Total]),
    FILTER(
        ALL('Forecast_Table'),
        'Forecast_Table'[Year] = EARLIER('Forecast_Table'[Year]) &&
        'Forecast_Table'[Product] = EARLIER('Forecast_Table'[Product]) &&
        'Forecast_Table'[Categorie] = EARLIER('Forecast_Table'[Categorie]) &&
        'Forecast_Table'[Month] >= EARLIER('Forecast_Table'[Month]) - 2 &&
        'Forecast_Table'[Month] < EARLIER('Forecast_Table'[Month]) &&
        'Forecast_Table'[State] = "Reel"
    )
)
))