Forum Discussion
Rolling Calculation
Hi,
I have a table as shown below that I would like to run some rolling months from.
I am fairly new to Power BI and Dax so struggling a little. Ideally I would like to display a graph that has a time filter, that will show the total number for the selected time period.
i.e. The WorkingDayActual_C would sum the numbers for the appropriate time span.
Am I able to do this just using this table? Do I need a date table to be linked and if so how would the formula work for this?
Any help would be very much appreciated.
Hi,
Unfortunately this website is blocked by our internbet security, so I am unable to view.
However, thanks to Bhavesh Patel, I have managed to understand the process more and seem to have found my solution:
NEWTEST_C = CALCULATE([TotalWorkingDaysActual_M],
DATESINPERIOD('KPI_ABS'[Month_Year],
LASTDATE('KPI_ABS'[Month_Year]),-11,MONTH
))This gives me the result I need each month.
Thank you Bhavesh for your patience and help.
Kind Regards
Rich.
15 Replies
Apologies, what I should have also stated, was that I would like to achieve a Rolling 12 month period.
I wasn't sure if I could do it using my Rolling Month column or not?
I have looked at some of the options available to me , but either can't quite get it to work or simply can't get things to work with my setup. :smileysad:
Hi There,
Date Table has a specific role in DAX Time intelligence calculations as It contains unique records of all the dates which is neccessary for the correct working of the time intelligence functions in DAX.
For creating a rolling 12 month average, there are variety of different ways you can deploy DAX Calculation.
Rolling Average 12 Months:=CALCULATE (SUM[Table[The WorkingDayActual_C],DATESBETWEEN (Date[DateKey],NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Date[DateKey] ) ) ),LASTDATE ( Date[DateKey] )))Thanks & Regards,
Bhavesh
Hope this would clarify your understanding.
Thanks & Regards,
Bhavesh
Hi,
That works for me if I just want to display the result as a number, but if I put it into a graph, it is showing just the monthly total for each month rather than calculating the 12m rolling period each month.
My apologies I added the 12 month rolling comment after I posted the message.
Can you suggest a solution for this scenario?
It is this section I seem to struggle resolving:
Date[DateKey],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Date[DateKey] ) ) ),LASTDATE ( Date[DateKey] )In essence the Date[DateKey] part.I have created a table using the following:DATE_RANGES = ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "dddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)This is linked to my table, but when I try to useeither the Date_Ranges table above or my current table in the Date[DateKey] section it just doesn't work.Rolling Average 12 Months = CALCULATE (
SUM('KPI_ABS'[WorkingDaysActual_C],
DATESBETWEEN (
Date[Month_Year],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Date[DateKey] ) ) ),
LASTDATE ( Date[DateKey] )
)
)I also see samples that start with Calendar, I feel stupid as I just can't work this one, I seem to hit a brick wall in my head!
Hi There,
'Date' is a Datetable and DateKey is the Date Column of your Date Table. in
'Date'[DateKey]
Be Cool. Just be with me. This will sort out soon.
Regards,
Bhavesh
