Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MrBrownn
Frequent Visitor

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

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 :

YearMonthProductCategorieStateTotal
20231Product1AReel0,815621764
20231Product2AReel2,298259863
20232Product1AReel7,563663234
20233Product1AReel1,486017377
20233Product2AReel6,973025905
20234Product1AReel2,936901503
20234Product2AReel1,566218564
20234Product3AReel9,793628798
20235Product1AReel3,723159548
20235Product2AReel3,652480619
20235Product3AReel1,108706899
20236Product1AReel9,037918128
20236Product2AReel8,793033488
20236Product3AReel4,46072489
20237Product1AReel9,566435301
20237Product2AReel7,605838191
20238Product1APredictionExemple here : AVG of 9,566435301 & 9,037918128 & 3,723159548)
20238Product2APrediction 
20238Product3APrediction 
20239Product1APrediction 
20239Product2APrediction 
20239Product3APrediction 
202310Product1APrediction 
202310Product2APrediction 
202310Product3APrediction 
202311Product1APrediction 
202311Product2APrediction 
202311Product3APrediction 
202312Product1APrediction 
202312Product2APrediction 
202312Product3APrediction 
20231Product1BReel17,72045003
20233Product2BReel43,24592979
20234Product1BReel81,56559688
20235Product3BReel97,98441339
20236Product1BReel79,95048349
20236Product2BReel43,98700767
20236Product3BReel48,04309131
20237Product1BReel91,17372428
20237Product2BReel60,25579445
20238Product1BPrediction 
20238Product2BPrediction 
20238Product3BPredictionAnd here (AVG of 48,04309131 & 97,98441339)
20239Product1BPrediction 
20239Product2BPrediction 
20239Product3BPrediction 
202310Product1BPrediction 
202310Product2BPrediction 
202310Product3BPrediction 
202311Product1BPrediction 
202311Product2BPrediction 
202311Product3BPrediction 
202312Product1BPrediction 
202312Product2BPrediction 
202312Product3BPrediction 

 

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.

 

1 ACCEPTED SOLUTION
v-jialongy-msft
Community Support
Community Support

 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"

    )

)

)

 

 

vjialongymsft_0-1704443699302.png

 

 

 

Best Regards,

Jayleny

 

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

View solution in original post

4 REPLIES 4
MrBrownn
Frequent Visitor

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.

v-jialongy-msft
Community Support
Community Support

 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"

    )

)

)

 

 

vjialongymsft_0-1704443699302.png

 

 

 

Best Regards,

Jayleny

 

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

Good morning @v-jialongy-msft,
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

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"
    )
)
))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.