Average for Month Excluding Current Month Per Product Description

[AartiD]

Suppose April is Current Month. Then Avg Sales per SKU will be Sum of Jan, Feb & March divided by 3. Now, if data is entered for May and May becomes Current Month , then Avg. Sales will be for Jan, Feb ,Mar, April divided by 4. , irrespective of sales for product is there in particular month. Average sales should change as and when month are selected or de-selected in Visual slicer.

I have Month as filter Slicer in Visuals of Power BI and I have to compare Current Month Sales to Avg.Previous Month Sales, hence to show Current Month data I have to select Current month say "April" in Filter Slicer, but Avg. figure should come as Average of Jan, Feb, Mar., it should not include April even if it is selected in Slicer.

Below is screen shot of excel file working and have also pasted same in case you want to incorporate data in excel. I have attached screen shot of Power Bi Visual as well. It is showing Avg. Sales till June (June beign current month) and average sales is caculated for month for which sales figures are available.It does not include months for which sales figure are not available for calculating average.

Hospital Name	Product Desc	Jan	Feb	Mar	April (Suppose Current Month)	Avg. Sales Excluding Current Month =Sum(Jan, Feb, Mar)/3
Maruti Hospital	Bandage				100	-
Maruti Hospital	HandwasH-8678	4000	7000	800		3,933
Maruti Hospital	Nova 18 Inj	100			500	33
Maruti Hospital	Operation Gowns				800	-
Maruti Hospital	Tapes 2'	2000	1000	500		1,167
Susruta Hospital	Bandage					-
Susruta Hospital	Cannula	200				67
Susruta Hospital	Nova 18 Inj		900	600	70	500
Susruta Hospital	Operation Gowns	500	100	50	50	217
Zen Hospital	Bandage	600		100	30	233
Zen Hospital	Drapes					-
Zen Hospital	Drapes-12'				500	-
Zen Hospital	Injection				90	-
Zen Hospital	Nova 18 Inj		2000			667

[wardy912 · Accepted solution]

Hi @AartiD 

 If you want this to work for previous months in the current year only, you will need to identify the current month and current year in your average calculation. For this to work you will need a date table with month numbers related to your sales table. Assuming you have that, here's the solution:

First, a measure to show the current month sales

Current Month Sales = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    Sales[Month] = SELECTEDVALUE('DateTable'[Month])
)

Then, the average calculation for previous months in the same year

Avg Sales Prev Months Same Year = 
VAR SelectedMonth = SELECTEDVALUE('DateTable'[MonthNumber])
VAR SelectedYear = SELECTEDVALUE('DateTable'[Year])
RETURN
    AVERAGEX(
        FILTER(
            ALL('DateTable'),
            'DateTable'[Year] = SelectedYear &&
            'DateTable'[MonthNumber] < SelectedMonth &&
            CALCULATE(SUM(Sales[SalesAmount])) > 0
        ),
        CALCULATE(SUM(Sales[SalesAmount]))
    )

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

[v-nmadadi-msft]

Hi @AartiD 

As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions  provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards

[v-nmadadi-msft]

Hi @AartiD 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[v-nmadadi-msft]

Hi @AartiD 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[sreejad · Accepted solution]

Hi @AartiD 

First create monthnumber column if its not already there and try below formula.

Measure = 
var MaxMonthNumber=MAX(Sheet1[Custom])
Var MonthCount=MaxMonthNumber-1
var Sales=CALCULATE(SUM(Sheet1[Value]),ALLEXCEPT(Sheet1,Sheet1[Hospital Name],Sheet1[Product Desc]),Sheet1[Custom]<MaxMonthNumber)
return
DIVIDE(Sales,MonthCount)

Custom is monthnumber, value is amount column.

Thanks.

[wardy912 · Accepted solution]

Hi @AartiD 

 If you want this to work for previous months in the current year only, you will need to identify the current month and current year in your average calculation. For this to work you will need a date table with month numbers related to your sales table. Assuming you have that, here's the solution:

First, a measure to show the current month sales

Current Month Sales = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    Sales[Month] = SELECTEDVALUE('DateTable'[Month])
)

Then, the average calculation for previous months in the same year

Avg Sales Prev Months Same Year = 
VAR SelectedMonth = SELECTEDVALUE('DateTable'[MonthNumber])
VAR SelectedYear = SELECTEDVALUE('DateTable'[Year])
RETURN
    AVERAGEX(
        FILTER(
            ALL('DateTable'),
            'DateTable'[Year] = SelectedYear &&
            'DateTable'[MonthNumber] < SelectedMonth &&
            CALCULATE(SUM(Sales[SalesAmount])) > 0
        ),
        CALCULATE(SUM(Sales[SalesAmount]))
    )

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!