cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RichWyeth Frequent Visitor
Frequent Visitor

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.

 

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
RichWyeth Frequent Visitor
Frequent Visitor

Re: Rolling Calculation

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 15
RichWyeth Frequent Visitor
Frequent Visitor

Re: Rolling Calculation

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. Smiley Sad

Super User
Super User

Re: Rolling Calculation

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

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
RichWyeth Frequent Visitor
Frequent Visitor

Re: Rolling Calculation

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?

RichWyeth Frequent Visitor
Frequent Visitor

Re: Rolling Calculation

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!

Super User
Super User

Re: Rolling Calculation

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

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
RichWyeth Frequent Visitor
Frequent Visitor

Re: Rolling Calculation

Hi,

 

I have selected my Date Ranges table and date, but still get errors:

Error.JPG

RichWyeth Frequent Visitor
Frequent Visitor

Re: Rolling Calculation

Hi,

 

I found the error, a missing bracket.

 

So I now have a graph that gives me the total. But it is giving me the total for the whole period for each month, rather than a rolling total.

 

i.e. each month is showing a total 674.

Super User
Super User

Re: Rolling Calculation

Hi There,

 

Please write two measures shown in the screen shot. It is tested and working fine. 

 

scr1.PNGscr2.PNG

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
RichWyeth Frequent Visitor
Frequent Visitor

Re: Rolling Calculation

I have written the following two measures:

 

 

TotalWorkingDaysActual_M = SUM(KPI_ABS[WorkingDaysActual_C])

 

 

Rolling Average 12 Months_M = CALCULATE (
    [TotalWorkingDaysActual_M],
    DATESBETWEEN(
        DATE_RANGES[Date],
  NEXTDAY( SAMEPERIODLASTYEAR ( LASTDATE ( DATE_RANGES[Date] ))),
        LASTDATE ( DATE_RANGES[Date] )
    )
)

 

I have  then created a graph and a time filter using the Date_Ranges[Date] for consistency. But my graph is still showing the over all total for each month.

 

Graph.JPG

 

Any test I do by turning measures to columns just show a value of 674 for each month?