Rolling Calculation

[RichWyeth]

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.

[BhaveshPatel]

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

[RichWyeth]

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!

[BhaveshPatel]

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

[RichWyeth]

Hi,

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

[BhaveshPatel]

Hi There,

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

Thanks & Regards,

Bhavesh

[RichWyeth]

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.

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

[rajannaap]

Hi ,

i need simillar help on my data

i have colums like 

Period        divsion   segment    net

201501          a              1              12332

201502          b              2              21233

..                    ...             ....               ......

201812          a              2              312333

I need to calcuate avg  net value (from 201701 to 201712)  for each division and segment by skipping recent 12 periods say (201801 to 201812)

your help would be appreciated.

Thanks

[v-haibl-msft]

@RichWyeth

A calendar table is necessary if we’d like to use the Time Intelligence Functions. Please take a look at the .pbix file provided by BhaveshPatel. It seems to work well. In case you still have problem with it, please post back.

Best Regards,

Herbert

[RichWyeth · Accepted solution]

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.

[iamvaish]

Hi Rich,

Could you please elaborate on the Month-year field that you have used.

Additionally would like to know,if you are using Measure or Column for Rolling period Calculation.

Awaiting your response

Kind Regards,

Vaishnavi

[BhaveshPatel]

Thank you Sir. Happy to hear that you found the solution. 

We are a great community of PowerBI.

Thanks & Regards,

Bhavesh

[BhaveshPatel]

Hi @RichWyeth

Please see the attached sample file.

https://drive.google.com/file/d/0B5-C_3XrFPdOX2pfVVNrSEVCeHM/view?usp=sharing

Thanks & Regards,

Bhavesh

[RichWyeth]

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.

[RichWyeth]

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]

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.