Forum Discussion

RichWyeth's avatar
RichWyeth
Frequent Visitor
9 years ago
Solved

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 ...
Need Help
  • RichWyeth's avatar
    RichWyeth
    9 years ago

    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.

BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

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's avatar
RichWyeth
Frequent Visitor
9 years ago

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's avatar
    BhaveshPatel
    Super User
    9 years ago

    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's avatar
      RichWyeth
      Frequent Visitor
      9 years ago

      Hi,

       

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

      • RichWyeth's avatar
        RichWyeth
        Frequent Visitor
        9 years ago

        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.