Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
RichWyeth
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

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.

View solution in original post

15 REPLIES 15
BhaveshPatel
Community Champion
Community Champion

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.

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

 

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.

Hi,

 

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

Error.JPG

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.

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?

 

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

@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

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.

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

 

 

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

We are a great community of PowerBI.

 

 

 

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.

Hi @RichWyeth

 

Please see the attached sample file.

 

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

 

 

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.

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.

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.