Average Sales (excluding "empty" months)

[bvanderwatt]

Good Day

PowerBi Link: https://drive.google.com/drive/folders/1l0H90UpW0LLzvOEnntJBa9eD2Kfimvss?usp=sharing

My measure works perfectly for combined branches of average units. 

However, when I split it by branch, the inidvidual branch average units doesn't equal the total ave units. I think this is due some months having no sales for some months. 

Ave units all branches: 871 

Ave Units: Asia (214) + Canada (315) + Europe (139) + South Africa (257) = 925 units

Below is my measure: 

Ave Qty Sold (Last 12 months) =

    averagex(

        summarize(

            calculatetable(

            'Calendar',

                Datesbetween(

                    'Calendar'[Date],

                        edate(eomonth(today(),-1)+1,-12),

                        eomonth(today(),-1))),'calendar'[Year],

                        'Calendar'[Month],

                        "ABCD",

                        [Inv+Order (Qty)]),

                        [ABCD]

        )

@Ashish_Mathur 

[Ashish_Mathur]

Hi,

Averages are never additive.  To get a grand average, one cannot add individual averages.  Therefore the results in your post might be correct.

[amitchandak]

@bvanderwatt , Assume you have measure unit ,

Then try measure like

averageX(Values(Date[YYYY-MMM]) , [Unit])

if you want to do it on a column

averageX(Values(Date[YYYY-MMM]) ,calculate(sum(Table [Unit])) )

This will sum till month level and Avg above it

[bvanderwatt]

Thank you so much. I need to use completed months. Therfore, I need to exclude August which is the current month. 

Please see below my formula. Hoping this will be a quick fix 🙂 

Ave Qty Sold (Last 12 months) =

    averagex(

        summarize(

            calculatetable(

            'Calendar',

                Datesbetween(

                    'Calendar'[Date],

                        edate(eomonth(today(),-1)+1,-12),

                        eomonth(today(),-1))),'calendar'[Year],

                        'Calendar'[Month],

                        "ABCD",

                        [Inv+Order (Qty)]),

                        [ABCD]

        )