Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi. I have a table where I will use one column for date and another column for values. I want to use values to calculate the rolling average of the preceding three annual periods. It means the last e caluendar years, for 2022, 2023 and 2024. How can I make a nes column or measure calculating the rolling average?
Solved! Go to Solution.
Hi @LFM - please find the attached file.
Proud to be a Super User! | |
I made a column shown under that shows average for all values before a certain date, and for every date it shows average of all values before that date. I think I will do this for the last 3 calendar years only, but cannot find a solution
Hi @LFM - If you don't already have a calendar table, create one to ensure accurate date handling
eg: CalendarTable = CALENDAR(MIN(YourTable[Date]), MAX(YourTable[Date]))
and create a relationship with date table , date column.
for rolling average for 3 yrs use the below calculation and replace the table name as per your model.
Proud to be a Super User! | |
Thanks for reply I will try it out. I need to ask, where did you create the column current week?: financials[Current Week]
you can try to create it in your date table.
Eg:
VAR RollingCount = COUNTROWS(SUMMARIZE(FilteredYears, YEAR(YourTable[Date])))
check this.
Proud to be a Super User! | |
could you please share the file you made with sample data?
Hi @LFM - please find the attached file.
Proud to be a Super User! | |
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
90 | |
67 | |
62 | |
53 |