Rolling month average in power bi dax

Question

I have these two columns: SALES_CALLSID QFQ4TA5006C2 QZPIOA18LW8A QS4GSA300PU0 ....; and MEETING_DATE 7/10/2014 12:00 3/27/2015 12:00 11/3/2015 12:00

I need the count of the first column (salesid) but this count should be a trailing twelve month average for every month, i.e. for example for July- the count of sales id of july + count of last 11 months (i.e. based on the meeting date column), similarly this measure has to be made for every other month.

The "Sales Call ID" column is a text field. I can see the count using the formula COUNTA(AccountSalesCalls[SALES_CALLSID])`.

The formula i built to get the total count of sales id for last 12 months is as follows:

CALCULATE (
    COUNTA ( AccountSalesCalls[SALES_CALLSID] ),
    DATESBETWEEN (
        AccountSalesCalls[MEETING_DATE],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( AccountSalesCalls[MEETING_DATE] ) ) ),
        LASTDATE ( AccountSalesCalls[MEETING_DATE] )
    )
)

When i do this i get an error:

A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN'. This is not supported.

I then built a DATE table and replaced MEETING_DATE column with column in DATE table as follows:

CALCULATE (
    COUNTA ( AccountSalesCalls[SALES_CALLSID] ),
    DATESBETWEEN (
        'Date'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( AccountSalesCalls[MEETING_DATE] ) ) ),
        LASTDATE ( AccountSalesCalls[MEETING_DATE] )
    )
)

This just gives me the count of that month which is the same result as COUNTA(AccountSalesCalls[SALES_CALLSID]).

I still haven't done the division part to get the average but first wanted to figure out how to make the total count work. Any help would be appreciated.

A simple and representative sample of your data could be useful in order to provide an answer or some guidance — alejandro zuleta
ok, so i have these two columns:SALES_CALLSID QFQ4TA5006C2 QZPIOA18LW8A QS4GSA300PU0 ; and, MEETING_DATE 7/10/2014 12:00 3/27/2015 12:00 11/3/2015 12:00 i need the count of the first column (salesid) but this count should be a trailing twelve month average for every month, i.e. for example for July- the count of sales id of july + count of last 11 months (i.e. based on the meeting date column), similarly this measure has to be made for every other month...i hope this clarifies... — Himanshu Malik
sorry i coudn't represent the information in column form, but its just two columns- 1.SALES_CALLSID followed by sample three values and then, 2. MEETING_DATE followed by sample three values... — Himanshu Malik
Edit your initial question, Check this How to edit your question — alejandro zuleta

user5226582 user5226582 · Accepted Answer · 2016-09-07T11:12:37

You were nearly there.

The calculated column:

Average = CALCULATE (
  COUNTA ( AccountSalesCalls[SALES_CALLSID] ) / 12,
  DATESBETWEEN(
    AccountSalesCalls[MEETING_DATE],
    DATEADD(AccountSalesCalls[MEETING_DATE], -12, MONTH),
    AccountSalesCalls[MEETING_DATE]
  ),
  ALL(AccountSalesCalls)
)

Note this divides by a constant (12), which would be incorrect for the first year of your data. You will have to decide how to deal with this (if at all).

One option could be to divide by the date range of existing selected data instead:

Average = CALCULATE (
  COUNTA ( AccountSalesCalls[SALES_CALLSID] ) / 
    DATEDIFF( MIN(AccountSalesCalls[MEETING_DATE] ),
      MAX( AccountSalesCalls[MEETING_DATE] ),MONTH),
  DATESBETWEEN(...

More or moving averages using DAX

Rolling month average in power bi dax

2 Answers