Next 3 months average

Sample 1.pbix (126.6 KB)

In the file attached, I want to get average of next 3 months Forecasted payments(excluding current month)

I understand a formula like this will work:
Next 3 month average = CALCULATE (
AVERAGE(‘To Collect’[To Collect] ),
DATESINPERIOD ( ‘Date’[Date], EOMONTH(TODAY(),0), 3, MONTH ))

But , in the current example Forecasted Payments formula is bit complicated, so I don’t know how to make it work in this case.

image

@Anu,

My recommendation here would be to upgrade your date table to @Melissa’s awesome extended date table. This will give you access to the offset functions, which will easily allow you to define your active period as the current month and the next three.

Here’s an excellent video she did on applying the offset functions:

If you have any problems after giving this a try, just give a shout.

– Brian

I have gone through the video, but DATESINPERIOD is much easier than this, I think,correct me if wrong?
In my case I just want a Card visualization, which shows from current date, +3 months(excluding current month)

CALCULATE (
AVERAGE(‘To Collect’[To Collect] ), // Just that I dnt know how to take AVERAGE(Forecasted Payments here
DATESINPERIOD ( ‘Date’[Date], EOMONTH(TODAY(),0), 3, MONTH ))

@Anu

Try This measure

Next 3 month average =
VAR MonthsProjection = 3
VAR NextThreeMonthsFC =
CALCULATE (
[Forecasted payments v2],
FILTER (
ALL ( ‘Date’[Date] ),
‘Date’[Date] > EOMONTH ( TODAY (), 0 )
&& ‘Date’[Date] <= EOMONTH ( TODAY (), MonthsProjection )
)
)
VAR Result =
DIVIDE ( NextThreeMonthsFC, MonthsProjection )
RETURN
Result

The PBIX file is also attached.

Let us know if this works or not.

Thanks.

Sample 1.pbix (126.9 KB)

Hi @Anu, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

The above formula works fine! Thanks!
But can this be made more dynamic?
Like if instead of next three months, I want previous three months?

@Anu

Yes, it can be made Dynamic. I have made use of the New Parameter and then added it as a slicer on the page. Then I modified the code and it’s all Dynamic now. Not just 3 Months, you can go back till 12 months and can also see Average Projection for coming 12 months.

Dynamic Average =
VAR SelectedMonths =
    SELECTEDVALUE ( 'Months Selection'[Months Selection] )
VAR ProjectedMonths =
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] > EOMONTH ( TODAY (), 0 )
            && 'Date'[Date] <= EOMONTH ( TODAY (), SelectedMonths )
    )
VAR PreviousMonths =
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] > EOMONTH ( TODAY (), SelectedMonths )
            && 'Date'[Date] <= EOMONTH ( TODAY (), 0 )
    )
VAR DynamicMonthsFC =
    IF (
        SelectedMonths >= 0,
        CALCULATE ( [Forecasted payments v2], ProjectedMonths ),
        CALCULATE ( [Forecasted payments v2] * -1, PreviousMonths )
    )
VAR Result =
    DIVIDE ( DynamicMonthsFC, SelectedMonths )
RETURN
    Result

Just One Tip, try to create a separate table for your measures as they will be easier to manage when you are working on a complex model.

Sample 1.pbix (128.4 KB)