Average sales last 6 months for completed months

[bvanderwatt]

Good Day 

Below is a screenshot of my query. 

This is currently calculating ave sales for June - November. 

However, November is not a "completed" month yet, therefore I would prefer to work out the average sales of the last "completed" 6 months (May - Oct.)

How can I amend my query? 🙂 

Ave Qty Sold (Last 6 months) =

CALCULATE(

SUM(

'Combined Sales'[QtyInvoiced]

),

DATESINPERIOD(

'Calendar'[Date],

MAX(

'Calendar'[Date]

),

-6,

MONTH

)

)

/6

[Ashish_Mathur · Accepted solution]

Hi,

Averagex (

summarize (

calculatetable (

'Calendar' ,

Datesbetween (

'Calendar'[Date] ,

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

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

'calendar'[Month name] ,

"ABCD" ,

[Sum of Inv+Order_Quantity]) ,

[ABCD]

)

My assumption is that Sum of Inv+Order_Quantity is a single measure.

[v-eqin-msft]

Hi @bvanderwatt ,

Not very clear. Did @Ashish_Mathur 's method work for you ?

Best Regards,
Eyelyn Qin

[bvanderwatt]

Hi. The solution didn't work. Did you imply that I was unclear? If so, I will explain better if needed. 🙂 

[Juventus]

Hi @bvanderwatt

Please refer below solutions.

1. Ensure you have your 'Calendar' table joined on [created date]

2. Add the below measure:

Average6M:= CALCULATE (
    AVERAGE( 'tableName'[columnName] ),
    DATESINPERIOD (
        'Calendar'[Date],
        LASTDATE ( 'Calendar'[Date] ),
        -6,
        MONTH
    )
)

Regards,

Juventus 

[bvanderwatt]

Hi. The last day in my calendar won't neccesarily be the today because I might have orders in the system to be dispatched in the upcoming months. Therefore, I don't think last date in the calendar table will work. 😞 

[Ashish_Mathur]

Hi,

Try these measures

Quantity invoiced = SUM('Combined Sales'[QtyInvoiced])

Quantity invoiced in last 6 months = averagex(summarize(calculatetable('calendar',datesbetween('calendar'[date],edate(eomonth(today(),-1)+1,-5),eomonth(today(),-1))),'calendar'[Year],'calendar'[Month name],"ABCD",[Quantity invoiced]),[ABCD])

Hope this helps.

[bvanderwatt]

Hi. What does the "ABCD" stand for? 

[Ashish_Mathur]

Hi,

It is the title of the virtual column which holds values of the measure "Quantity invoiced".  Does my solution work?

[bvanderwatt]

Hi. I am sorry, but I don't understand the "ABCD" virtual column. 

My error is starting at the "ABCD." 

[Ashish_Mathur]

Hi,

Do not close the bracked after 'Calendar'[Month Name].  After ABCD should be the measure.  There should be a closing bracket after the mesure.

[bvanderwatt]

Quantity invoiced in last 6 months =

averagex(

summarize(

calculatetable(

'Calendar',

Datesbetween(

'Calendar'[Date],

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

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

'calendar'[Month name],

"ABCD",

[Sum of Inv+Order_Quantity],

[ABCD]

))

[Ashish_Mathur · Accepted solution]

Hi,

Averagex (

summarize (

calculatetable (

'Calendar' ,

Datesbetween (

'Calendar'[Date] ,

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

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

'calendar'[Month name] ,

"ABCD" ,

[Sum of Inv+Order_Quantity]) ,

[ABCD]

)

My assumption is that Sum of Inv+Order_Quantity is a single measure.

[bvanderwatt · Accepted solution]

You are amazing. Thank you very much. There's just a slight variance. 

The average should be 263 266 but the formula above gived me 259 018.

It's not linked to the data slicer either. Any chance you know what is causing this variance? 

[Ashish_Mathur]

Thank you.  The figure of 259,018 is absolutely correct.  That is average from October to Feb.

[bvanderwatt]

WOW! You are amazing. I amended the "5" to "6" and it worked perfectly. Thank you so much. I really appreciate all your help and advise. Thank you for taking the time to help me. 

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.

[bvanderwatt]

Good Day Ashish aka GENIUS 🙂

Hoep you're very well. 

I'm hoping you could point me in the right direction. 

The below measure is the one we worked on together. 

However, it's skipping months that had no sales. How could I amend the formula to include the empty months. It's skewing my results. 

I tried SumX instead of AverageX, but must be doing something wrong. 

Hoping you could save the day once again. 🙂 

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]

Thank you for your kind words.  I am just a leaner.  Share some data, describe the question and show the expected result.

[bvanderwatt]

I've created a new post. 🙂 

https://community.powerbi.com/t5/Desktop/Average-Sales-excluding-quot-empty-quot-months/m-p/2706738#...

[bvanderwatt]

Thank you so much for always helping me. I can't attach the PowerBi File. Can I email it to you? 

Ave Qty for last 12 months = 871

However when I look at the ave per branch and sum it, it doesnt equal 871. I think it's because of the blank months. 

[Ashish_Mathur]

You are welcome.