DAX - Rolling 12 Mo.:FIND FIRST YR WITH DATA|Power Pivot|Excel Forum|My Online Training Hub

Hi Mynda...

I am wanting to calculate a rolling 12 month (Moving Average). I found this formula (see below) and thought it would work. The if statement removes the first 12 months so that the MA calculation includes 12 months, and removes dates that have not occurred yet. The IF statement assumes all the data starts in the same year. I filter my by different regions and the starting dates are different. Some start in 2013. Others start in 2015, etc. They all start at the beginning of the year (Jan).

I have tried multiple times to change the IF statement. I highlighted in RED where I think my problem is. What I am trying to do is to find the first date with data then find the year value and add one year. So...if the first date with data is 01/01/2016 then I want to only calculate the MA from 01/01/2017 to current date.

Thanks in advance. And, Happy Easter!

Kind regards,

...Harry

R12RTOT:=IF (

    AND (

        SUM ( 'Rome Data'[Revenue] ) > 0,

        MAX ( 'Date Table'[Year] )

            > CALCULATE ( MIN ( 'Date Table'[Year] ), ALL ( 'Date Table'[Year] ) )

    ),

    CALCULATE (

        SUM ( 'Rome Data'[Revenue] ),

        DATESINPERIOD ( 'Date Table'[Date], LASTDATE ( 'Date Table'[Date] ), -1, YEAR )

    )

)