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

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?

 

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

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

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

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

    We are a great community of PowerBI.

     

     

     

    Thanks & Regards,

    Bhavesh

  • RichWyeth's avatar
    RichWyeth
    Frequent Visitor
    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.

  • iamvaish's avatar
    iamvaish
    Regular Visitor
    9 years ago

    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