Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Next 3 month Average

PBIX link: https://drive.google.com/file/d/1UKVVjEndFDuBUnsOAGVbg_CnweY2cR-d/view?usp=sharing   In the file attached, I want to get the average of next 3 months Forecasted payments(excluding curre...
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Please try this measure expression to get the 3 month average.

 

FP 3 Month =
VAR maxdate =
    MAX ( 'Date'[Date] )
VAR startnextmonth = maxdate + 1
VAR end3month =
    EOMONTH (
        startnextmonth,
        2
    )
RETURN
    CALCULATE (
        AVERAGEX (
            DISTINCT ( 'Date'[MonthInCalendar] ),
            DIVIDE (
                [Incremental POC],
                1 - [LastMonthPOC]
            ) * 474
        ),
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] >= startnextmonth
                && 'Date'[Date] <= end3month
        )
    )

 

Regards,

Pat