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,

 

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?

 

  • 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

    • 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.

      • 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

  • rajannaap's avatar
    rajannaap
    Regular Visitor
    7 years ago

    Hi ,

     

    i need simillar help on my data

     

    i have colums like 

    Period        divsion   segment    net

    201501          a              1              12332

    201502          b              2              21233

    ..                    ...             ....               ......

    201812          a              2              312333

     

    I need to calcuate avg  net value (from 201701 to 201712)  for each division and segment by skipping recent 12 periods say (201801 to 201812)

     

    your help would be appreciated.

    Thanks