cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamsonAnalytics Frequent Visitor
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:

cap.PNG

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:

cap2.PNG

I'm expecting an output like this

cap3.PNG

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!
 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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.

6moConsecutive.jpg

My sample file is attached for you to look at.

 

View solution in original post

6 REPLIES 6
Super User
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.

6moConsecutive.jpg

My sample file is attached for you to look at.

 

View solution in original post

amitchandak Super Contributor
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
Super User

Re: Average of the last 6 months

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
SamsonAnalytics Frequent Visitor
Frequent Visitor

Re: Average of the last 6 months

Hi jdbuchanan71 this worked perfectly as I needed.

 

Thank you!!

SamsonAnalytics Frequent Visitor
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
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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

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

Difinity Conference

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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)