FY Year-to-date rolling average - DAX help

[Anonymous]

I have been asked to add a rolling year-to-date average in a PBI report I have. I think I already have all of the info I need in this table, but am enough of a DAX newbie that I am not sure how I'd compose the code.

In my report each row is a month. I have another column called "Accounting Year" which just repeats the FY. So for example, from October 2014 through September 2015 (our fiscal year) it just says "2015." The next row (October) it says "2016" and continues for the next 11 rows.

So what kind of DAX would you use that makes a running average from the first row that has the same value in Accounting Year as the current row, of course looking back only?

[Anonymous · Accepted solution]

Amazingly I managed to figure it out myself, so thought I'd share my solution.

As I said in original query I thought I had all the info I needed, and I was right. There was another column I didn't mention that was called "Accounting Period" that essentially places a "1" in rows starting with October, "2" in November, etc. through "12" in September. Using this column I came up with:

PPM YTD NA = 
IF(ISBLANK('%COPQ'[Monthly PPM NA]),BLANK(),
    ((CALCULATE (
    AVERAGEX ( '%COPQ', '%COPQ'[Monthly PPM NA] ),
    DATESINPERIOD (
        '%COPQ'[Date],
        LASTDATE ( '%COPQ'[Date] ),
        -('%COPQ'[Accounting Period]),
        MONTH
    )))))

I am surprised I got it so quickly, but very happy!