Average of the last 6 months

[SamsonAnalytics]

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?

I already try this:

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

Thank you!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[SamsonAnalytics]

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.

[Ashish_Mathur]

Hi,

Check both those tables and review the relationships.

[amitchandak]

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

[jdbuchanan71 · Accepted solution]

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.

[SamsonAnalytics]

Hi jdbuchanan71 this worked perfectly as I needed.

Thank you!!