Calculate average since x month prior to a certain date

[rginny_]

Hi All,

I have two card visuals and an 'after' date slicer in my report. When you set the date slicer to a certain day, one of the cards shows the monthly average of total users prior to the selected date and the other card shows the monthly average of total users since the selected date.

The 'since' measure looks like this:

TUAvg =

AVERAGEX(

SUMMARIZE(dates,dates[Month]),

[Total Users])

The 'before' measure looks like this:

TUAvg Before =

CALCULATE(

AVERAGEX(

SUMMARIZE(dates,dates[Month]),

[Total Users]),

FILTER(ALL(dates),

dates[date]<MIN(dates[date])))

I am now trying to change the 'before' measure so that it only takes the average of the same amount of time as the 'since' measure would. So, for example, if my date slicer is set to 10 September 2020 then my 'since' measure will average the total users of 3 months up until today's date (Sept <10 Sept onwards>, Oct, Nov - 68 days). In this case, I would want my 'before' measure to only average the total users of 3 months prior to 10 Sept (Sept <prior to 10 Sept>, Aug, Jul <4 Jul onwards> - 68 days) instead of averaging all the months prior to 10 Sept.

Essentially, I would want the 'before' average to always be based on the same amount of time as the 'since' average, which is Today's Date - Filtered Date.

I hope I explained it well - I don't think it's too complex but I cannot figure out how to do it. Thank you so much for any help in advance!

[v-alq-msft · Accepted solution]

Hi, @rginny_ 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDAR(DATE(2019,1,1),DATE(2020,12,31))

There is no relationship between tables. You may create two measures as below.

Before Measure= 
var selecteddate = SELECTEDVALUE('Calendar'[Date])
var diff = TODAY()-selecteddate
var beforedate = selecteddate-diff
return
AVERAGEX(
    FILTER(
        ALL('Table'),
        [Date]>=beforedate&&
        [Date]<=selecteddate
    ),
    [Value]
)

Since Measure = 
var selecteddate = SELECTEDVALUE('Calendar'[Date])
return
AVERAGEX(
    FILTER(
        ALL('Table'),
        [Date]>=selecteddate&&
        [Date]<=TODAY()
    ),
    [Value]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CNENFRNL]

Hi, @rginny_ , it's inferred from your description that Dates[Date] extends from 2020/7/4 to today (2020/11/17), isn't it? You might want to try a measure leveraging some time intelligence function in DAX,

TUAvg Before =
VAR __period =
    DATESBETWEEN (
        Dates[Date],
        STARTOFMONTH ( DATEADD ( Dates[Date], -2, MONTH ) ),
        MAX ( Dates[Date] ) - 1
    )
RETURN
    AVERAGEX ( [total users], __period )

and accordingly,

TUAvg Since =
VAR __period =
    DATESBETWEEN (
        Dates[Date],
        MAX ( Dates[Date] ) + 1,
        ENDOFMONTH ( DATEADD ( Dates[Date], 2, MONTH ) )
    )
RETURN
    AVERAGEX ( [total users], __period )

[NamishB]

HI @rginny_ - Can you post some data to explain more?

and also whats your expected outcome?

Also try one minor tweek in your measure and compare the results :

TUAvg Before =
CALCULATE (
    AVERAGEX ( SUMMARIZE ( dates, dates[Month] ), [Total Users] ),
    FILTER (dates, dates[date] < MIN ( dates[date] ) )
)

If this does'nt solve the purpose, please post data.

Cheers,

-Namish B