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.

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.

    • BhaveshPatel's avatar
      BhaveshPatel
      Super User
      9 years ago

      Hi There,

       

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

       

       

      Thanks & Regards,

      Bhavesh

      • RichWyeth's avatar
        RichWyeth
        Frequent Visitor
        9 years ago

        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?