Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |