cancel
Showing results for
Did you mean:
Frequent Visitor

## Average of the last 6 months

Hello everyone,

I have a table with employee ID, Date and Expense. I need to know who is spending money in a consecutive period of 6 months starting from TODAY (this must be updated automatically), and in average how much are they spending monthly.

This is my data:

In this case employee 002 is in red because is the only one that had expenses for 6 consecutive months starting from today (09/27/2019). Since his average monthly expenses were:

I'm expecting an output like this

Any ideas on how to obtain it?

6MonthAvg = CALCULATE(SUM('TableName'[Expense Amount]), DATESINPERIOD('TableName'[Expense Date], DATEADD(LASTDATE('TableName'[Expense Date]),0, MONTH), -6, MONTH))/DISTINCTCOUNT('TableName'[_Month])

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Average of the last 6 months

Hello @SamsonAnalytics

Take a look at the attached file.  I added a date table so we can use it to count months then a couple measures to get our amount.

One to calc the 6 month amount per employee and then anothe to correctly sum that amount.

```Avg 6mo continuous =
VAR _6MonthDates =
DATESINPERIOD ( Dates[Date], TODAY (), -6, MONTH )
VAR _MonthsInLast6 =
CALCULATE (
COUNTROWS ( SUMMARIZE ( Dates, Dates[Month Year] ) ),
Expenses,
_6MonthDates
)
RETURN
IF (
ISINSCOPE ( Expenses[Employee ID] ) && _MonthsInLast6 = 6,
CALCULATE ( AVERAGEX ( VALUES ( Dates[Month Year] ), [Expense] ), _6MonthDates ),
BLANK()
)```
`6mo continuous totaled = SUMX ( VALUES ( Expenses[Employee ID] ), [Avg 6mo continuous] )`

I could not get the first measure to sum correctly, the is why I used a 2nd one for the totaling.

My sample file is attached for you to look at.

6 REPLIES 6
Super User

## Re: Average of the last 6 months

Hello @SamsonAnalytics

Take a look at the attached file.  I added a date table so we can use it to count months then a couple measures to get our amount.

One to calc the 6 month amount per employee and then anothe to correctly sum that amount.

```Avg 6mo continuous =
VAR _6MonthDates =
DATESINPERIOD ( Dates[Date], TODAY (), -6, MONTH )
VAR _MonthsInLast6 =
CALCULATE (
COUNTROWS ( SUMMARIZE ( Dates, Dates[Month Year] ) ),
Expenses,
_6MonthDates
)
RETURN
IF (
ISINSCOPE ( Expenses[Employee ID] ) && _MonthsInLast6 = 6,
CALCULATE ( AVERAGEX ( VALUES ( Dates[Month Year] ), [Expense] ), _6MonthDates ),
BLANK()
)```
`6mo continuous totaled = SUMX ( VALUES ( Expenses[Employee ID] ), [Avg 6mo continuous] )`

I could not get the first measure to sum correctly, the is why I used a 2nd one for the totaling.

My sample file is attached for you to look at.

Super Contributor

## Re: Average of the last 6 months

You to put condition in month count too

```6MonthAvg = CALCULATE(SUM('TableName'[Expense Amount]), DATESINPERIOD('TableName'[Expense Date], DATEADD(LASTDATE('TableName'[Expense Date]),0, MONTH), -6, MONTH))
/if (CALCULATE(distinctcount('TableName'[_Month]), DATESINPERIOD('TableName'[Expense Date], DATEADD(LASTDATE('TableName'[Expense Date]),0, MONTH), -6, MONTH))
=6 ,CALCULATE(distinctcount('TableName'[_Month]), DATESINPERIOD('TableName'[Expense Date], DATEADD(LASTDATE('TableName'[Expense Date]),0, MONTH), -6, MONTH))
,blank()
)```
Super User

## Re: Average of the last 6 months

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Average of the last 6 months

Hi jdbuchanan71 this worked perfectly as I needed.

Thank you!!

Frequent Visitor

## Re: Average of the last 6 months

Hi Ashish_Mathur,

I downloaded your attached file but I couldn't make the measure work. Not even with the example that you left me in the first tab.

I tried to use the same measure with my data and I couldn't either.

Thank you for the interest in helping me.

Super User

## Re: Average of the last 6 months

Hi,

Check both those tables and review the relationships.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)