Trailing Twelve Months SUM not calculating in powerBI correctly

1
votes

Trying to add a measure in PowerBI that calculates the rolling 12-month sum of sales and the measure works fine up until the most recent 12 months worth of data. Not sure what's causing this error. Below is the data and code in PowerBI I'm using. 

TTM MRR =
CALCULATE (
    SUM ( MRR[MONTHLY_REV] ),
    FILTER (
        ALL ( MRR[CLOSE_MONTH] ),
        AND (
            MRR[CLOSE_MONTH] <= MAX ( MRR[CLOSE_MONTH] ),
            DATEADD ( MRR[CLOSE_MONTH], 1, YEAR ) > MAX ( MRR[CLOSE_MONTH] )
        )
    )
)

Data:

[Excel Data]

1
powerbidax

1 Answers

0
votes

It might be easier with DATESINPERIOD.

TTM MRR =
VAR PeriodEnd = MAX ( MRR[CLOSE_MONTH] )
RETURN
    CALCULATE (
        SUM ( MRR[MONTHLY_REV] ),
        DATESINPERIOD ( MRR[CLOSE_MONTH], PeriodEnd, -12, MONTH )
    )