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' 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?