Rolling 12 month average not working if no values in month

[waldnerr]

Hi,

i have calculated the rolling 12 month average with subsequent DAX formulas for Amount and Hit Rate:

Amount (Euro, R12M) =

VAR NumOfMonth = 12

VAR LastCurrentDate =

    max('Date (Date Stage Set)'[Date])

VAR Period =

        DATESINPERIOD (

        'Date (Date Stage Set)'[Date],

        LastCurrentDate,

        - NumOfMonth,

        MONTH

    )

VAR Result =

    CALCULATE (

        AVERAGEX ( VALUES ('Date (Date Stage Set)'[YYYY/MM] ),[Amount (Euro, Measure)]),

        Period

    )

VAR FirstDateInPeriod =

    MINX ( Period, 'Date (Date Stage Set)'[Date] )

VAR LastDateWithSales =

    MAX ( Opportunity[Date Stage set] )

RETURN

    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

Hitrate Amount (Euro, R12M) =

VAR NumOfMonth = 12

VAR LastCurrentDate =

    max('Date (Date Stage Set)'[Date])

VAR Period =

    DATESINPERIOD (

        'Date (Date Stage Set)'[Date],

        LastCurrentDate,

        - NumOfMonth,

        MONTH

    )

VAR Result =

    CALCULATE (

        AVERAGEX ( VALUES ('Date (Date Stage Set)'[YYYY/MM] ), [Hitrate Amount (Euro)]),

        Period

    )

VAR FirstDateInPeriod =

    MINX ( Period, 'Date (Date Stage Set)'[Date] )

VAR LastDateWithSales =

    MAX ( Opportunity[Date Stage set] )

RETURN

    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

The result looks ok:

When i introduce the Stage as column legend, the amount is not calculated correct anymore - i assume, if there is not a value available for each Stage in a specific month:

I would apprechiate your can help me to adapt the formula accordingly to make it work.

Thank you,

Best regards,

Reinhard

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...