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

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?